Reply
Mon 15 Dec, 2014 10:46 pm
Hi, I'm feeling like an idiot tonight! Maybe someone can help me figure this out?
Suppose you have a list of values in a spreadsheet that add up to, say, 1000. Something like:
800
150
3
2
10
10
10
15
Now say you want to rate each of those values according to where they would fall in standard of deviation groupings so that the 900 and 150 would both rate as "0.5" (these being within half a standard of deviation) and the 2 as σ3.5 and so on with each value being bracketed to within a σ0.5 values.
This table will be used to classify frequency of records in a database from "most common" to "rarest" with other intermediate ratings in between. Something tells me that this should be relatively easy to figure out but I can't seem to make it work. Any help would be really appreciated!
I think I figured this out myself albeit in a crude, inelegant and semi-manual manner but in case anyone else has a similar project here's what I did:
I made a column that computes the values in the first cell so that it figures out the percentage of the total. In this case there were 477474 records so =A1/477474*100 and then copied that cell's contents to the rest of the column. Next, the manual part, I simply dragged down the range of cells in this column until the sum reaches 54%. This is the first group (most common) with a value of 0.5sd. To find out the next group I drag to 68% and so on.
Well, I think that sort of does it!
Stumble It!
Tweet This
Bookmark on Delicious
Share on Facebook
Share on MySpace