Create summary of quantities by an interval

**SueWithQuestion** · 06-20-2011, 09:01 AM

THANK YOU!!!

Attached is a sample data file.

The worksheet Raw Data contains a column, Number, that is just a sequential number from 1, to a variable ending number. The column Store contains one of a variable number of possible names. I am only interested in 3 specific Store names: smith, jones, and brown.

I'd like to be able to create the Summary Data worksheet that is shown. However, in what I've shown I listed the interval 301 - 400 as the last interval of interest; in reality, I'd like to have a formula (or macro?) to determine the last interval. I'd also like to fill each cell in that table with the number of occurrences of the specified store in the specified interval.

I hope I'm being clear.

What is the best way to do this?

**NBVC** · 06-20-2011, 09:07 AM

Where are the intervals coming from and what exactly is going in the table of results?

**SueWithQuestion** · 06-20-2011, 09:13 AM

Where are the intervals coming from and what exactly is going in the table of results?

I apologize. I should have been clearer.

The Stores are actual in a rank order. The intervals are fixed in that they are by 100.

I want to know how many "smith" stores occur in the top 100, in the next 100 after that, in the next 100, etc., and the same for the jones and brown Store values.

Make sense?

**NBVC** · 06-20-2011, 09:33 AM

See attached to see if it suits.

The range was divided into separate cells so that formulas could more easily reference actual numbers....

So Summary sheet, A2, formula is:

=IF(MAX('Raw Data'!A:A)>=0,0,"")

in A3:

Please Login or Register  to view this content.

copied down as far as you want.

in B2:

Please Login or Register  to view this content.

in B3:

Please Login or Register  to view this content.

copied down as far as adjacent column.

in C2:

Please Login or Register  to view this content.

copied down and across the 3 columns

Note: You can create a dynamic named range called: LRow with formula:

INDEX('Raw Data'!$B:$B,MAX('Raw Data'!$A:$A) and replace occurances of that part in the formula above with Lrow