Calculating a sliding scale

[John]

I have a column of 2000 dollar amounts that I need to break up into 6
groupings. Obviously I could divide the number of rows by 6 and get my
intervals, in this case 300.
However, I need to base these intervals , not on the count but on the dollar
amount. The dollar amounts range from $5000.000 to 0.

One might suggest that dividing 5000 by 6 would work. But I do not want
equal parts. The median value of all items is 870, not 2500. I need to
create a sliding scale of sorts.

Does anyone have any ideas? Or the magic algorhythm.

I was originally thinking of spliting the values in half using median(),
then splitting the two halves in haf, then splitting those 4 in half, then
those 8 in half... you get the idea.
I don't think that will work either as:
2 -4 -8- 16 - 32 - 64 - 128 - 256 - 512 - 1024 - 2048...

I could break it up into 2048 pieces and still those peices wouldn't be
divisible by 6... Ahhhhhh

Help, Please

 

[Bernie Deitrick]

John,

If you have the values in column A, starting in row 2, then in B2, enter the
formula

=MIN(INT(SUM($A$1:A2)/SUM(A:A)*6)+1,6)

and copy down to match column A.

HTH,
Bernie
MS Excel MVP

 

[John]

I am assuming that the values need to be in ascending order for this formula
to work?
The formula works great.. Thanks. But I'm not sure it is giving me what I
was looking for.

Let me explain a little more. I have 1800 sores with 1800 different annual
purchase amounts for 2006. These values range from $0 to $4880. The median
value is $880.00. The upper half is dominated with $1000-$2000 values with
only 6 values over 2000 and the lower half is dominated with $ 500 - $800
values.

What I was looking to do is rate each store based on its purchases, grouping
these stores into 6 distinct groups. Groups A,B,& C would be performing
stores, while D, E, & F would be non performing stores. I would like a
graduated scale so that the majority of like values would be the average
range; therefore having a "C" Status.

Does this make any sense?

 

[Bernie Deitrick]

John,

Perhaps normalize the value based on MEDIAN and STDEV? In B2, enter this and copy down - lower
numbers are poorer performers:

=INT((A2+MEDIAN($A$2:$A$1800))/STDEV($A$2:$A$100))-INT((MIN($A$2:$A$1800)+MEDIAN($A$2:$A$1800))/STDEV($A$2:$A$1800))+1


HTH,
Bernie
MS Excel MVP