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


"John" wrote in message
...
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

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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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,

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


"John" wrote in message ...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message ...
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