Calculating a sliding scale

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
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?
 
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
 
Back
Top