Finding Decile Values

M

Marston

I have two columns of data. The first represent a running count of
weeks, the second the number of people in that group.

Example:

0 100,000
1 40,000
2 38,000
3 76,000
4 45,000
5 92,000

etc.

I'd like to find out the weighted value across each decile. For
instance, in the above example, there are 391,000 people

0 - 39,100: 0.0
39,101 - 78,200: 0.0
78,201 - 117,300 = ((100,000-78200) * 0 + (117,300-100000)*1)/39100
117,301- 156,400 = ((156400-140000)*2+(140000-117301)*1))/39100

Does this make sense. Doing this by hand is way to time consuming
because the breakpoints move around.
 
R

recrit

I have two columns of data. The first represent a running count of
weeks, the second the number of people in that group.

Example:

0    100,000
1      40,000
2      38,000
3      76,000
4      45,000
5      92,000

etc.

I'd like to find out the weighted value across each decile. For
instance, in the above example, there are 391,000 people

0 - 39,100: 0.0
39,101 - 78,200: 0.0
78,201 - 117,300 = ((100,000-78200) * 0 + (117,300-100000)*1)/39100
117,301- 156,400 = ((156400-140000)*2+(140000-117301)*1))/39100

Does this make sense. Doing this by hand is way to time consuming
because the breakpoints move around.
Click to expand...

if i understand correctly, the function FREQUENCY can help.... look
under Excel help it explains what it needs and its return values. It
takes a data array (your by week data) and a bins array (your defined
deciles). The function will return the count for each bin, then you
can use that count to get your weighted value. If you set up the bin
array in column as:

0
39,100
78,200
117,300
156,400

The function will return an array of counts for each of these
numbers. Count is the count for the interval... for example (0 to
39,100], beginning exclusive of previous bin and inclusive of current
bin. The returns 1 more than the number of bins that would represent
anything data > the last bin.

Excel Help can clear up anything usage of this function.
 
Top