Calculating a sliding scale
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 |
Calculating a sliding scale
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 |
Calculating a sliding scale
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 |
Calculating a sliding scale
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 |
All times are GMT +1. The time now is 11:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com