But good news: we are moving forward with the change to add =iferror to the formula. It'll take a min, but itll be a huge time saver long term.

Thanks again for your help, guys

Sounds like a strong setup.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

Excel people - I call on you for help with what I suspect is an easy forumla I just can't wrap my head around.

I have 18,000+ rows of simple number (no decimal points). I need to gather data on how many of these are <= 30, 31-60, 61-90 etc. etc. etc.

Help, please?

Are you very good with pivot tables? You could create a pivot table, then set the rows to grouping by those intervals. That's the easiest way.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

wait shit, roger can correct me but I don't think you can use a dual conditional (like 4<X<10) in a countif

You can but he'd have way too many iterations to get up to 4,456. It takes 149 intervals to get to 4,456 in groups of 30.

Quote:

Originally Posted by swordo84

4456 .

You're going to need to use a pivot table. It's really not difficult.

I'm going to assume your data is set up in a traditional spreadsheet format.

Select cell A1 in that worksheet.
Go to Insert and then click on pivot table (It's just under and to the left of the Insert tab of the ribbon)
A new little screen will appear - just click on okay
That will create a new worksheet (don't worry, nothing happens to the old worksheet. it's still fine)
You will see a menu on the right that says Pivot Table Fields
Select the column with your number and drag it down to the box where it says "rows"
Select the column again and drag it to where it says "values"

You will see the pivot table start to the build on your left.

There should be two columns in your pivot table...

Row Labels & Sum of (Whatever is the name of your column)

Right click on the Row Labels column and select "Group" and enter
Starting at 1
Ending at 5000
By 30

Right Click on "Sum of (Whatever is the name of your column)"
Summarize values by - Count

You're done.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

You can but he'd have way too many iterations to get up to 4,456. It takes 149 intervals to get to 4,456 in groups of 30.

You're going to need to use a pivot table. It's really not difficult.

I'm going to assume your data is set up in a traditional spreadsheet format.

Select cell A1 in that worksheet.
Go to Insert and then click on pivot table (It's just under and to the left of the Insert tab of the ribbon)
A new little screen will appear - just click on okay
That will create a new worksheet (don't worry, nothing happens to the old worksheet. it's still fine)
You will see a menu on the right that says Pivot Table Fields
Select the column with your number and drag it down to the box where it says "rows"
Select the column again and drag it to where it says "values"

You will see the pivot table start to the build on your left.

There should be two columns in your pivot table...

Row Labels & Sum of (Whatever is the name of your column)

Right click on the Row Labels column and select "Group" and enter
Starting at 1
Ending at 5000
By 30

Right Click on "Sum of (Whatever is the name of your column)"
Summarize values by - Count

You're done.

Ok, great - thanks for the help. I'm about the leave for the weekend so this is now a Tuesday project (yay for Canadian long weekends!)

If I still have troubles on Tuesday I'll reach out - thanks again!

__________________
Eat Drink & Be Merry

Quote:

Originally Posted by ARFiredancer

...I don't know what I am trying to say...I shouldn't post when I am high........

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

Did a google search, but Im not really finding specifically what Im looking for.

Anyone here know how to do Freeze Panes on multiple sections? So...what Im looking for is rows 1-6 to be frozen. Easy enough.

But then when I scroll down to row 474, I want 474-479 to take the place of the first freeze. They are similar headlines, so rows 1-6 can fall out of view and 474-479 will replace as the frozen section.

Then this same scenario actually needs to happen one more time with rows 947-952.

Did a google search, but Im not really finding specifically what Im looking for.

Anyone here know how to do Freeze Panes on multiple sections? So...what Im looking for is rows 1-6 to be frozen. Easy enough.

But then when I scroll down to row 474, I want 474-479 to take the place of the first freeze. They are similar headlines, so rows 1-6 can fall out of view and 474-479 will replace as the frozen section.

Then this same scenario actually needs to happen one more time with rows 947-952.

Any help is appreciated!

I know what you're talking about, and I have no idea. I can have rows 1-6 frozen (pretty sure that feature is still available, haven't used it for a few of the later versions of excel but there's zero reason for them to take it out), but I don't know how to replace that with the new rows when you scroll down.

__________________
#BeltMelt2k14 never forget

Tyler made me change my sig and I did because you just don't fuck with poop-apples.

Did a google search, but Im not really finding specifically what Im looking for.

Anyone here know how to do Freeze Panes on multiple sections? So...what Im looking for is rows 1-6 to be frozen. Easy enough.

But then when I scroll down to row 474, I want 474-479 to take the place of the first freeze. They are similar headlines, so rows 1-6 can fall out of view and 474-479 will replace as the frozen section.

Then this same scenario actually needs to happen one more time with rows 947-952.

Any help is appreciated!

Why do you have multiple column headings in the same columns? Makes no sense.

Okay, that said, the only way to do this is with a macro. Is that okay?

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

Related question, but without multiple column headings in the same column:

Anyone know how to get the column headings to replace the, A, B, C etc...labels when you scroll down? I saw that in a spreadsheet recently and thought it was cool.

It's in Excel Options....

Otions - Advanced - Display options for this worksheet

Uncheck the first box that says show row and column labels.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

If you do that, you will no longer see A, B, C across the top of your spreadsheet. You will only see the contents that's in Row1 of that spreadsheet.

That said, this will remove the 1, 2,3 etc.. on the rows. It removes them both from view.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

Sorry, the last step is to uncheck "show row and column headers" not labels.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

The spreadsheet may have been using a table.
Highlight your range (or the entire worksheet); format as a table.
After you start scrolling down, you can't see the A,B,C,D column labels anymore.

Yeah, this is the only other option I can think of right now.

__________________

Quote:

Originally Posted by opshannon

You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.