inverse weighted average

[Dan Cotts]

I am trying to figure out how to do a weighted average using a reverse ratio
- that is, I have members of a group, each with Income, that I need to
allocate items among the group - but the members with lower income should
have MORE allocation made to them.

 

[Jim Cone]

One way...
'--
INCOME FACTOR PERCENT
-------------------------------------
$10000 6.1 22.86%
$15000 4.1 15.24%
$12000 5.1 19.05%
$8000 7.6 28.57%
$16000 3.8 14.29%
------------------------------------
$61000 26.7 100.00%

Factor is total group income divided by individual income.
Percent is Factor divided by Factor total.
'--
Jim Cone
Portland, Oregon USA




"Dan Cotts"
<[email protected]>
wrote in message
I am trying to figure out how to do a weighted average using a reverse ratio
- that is, I have members of a group, each with Income, that I need to
allocate items among the group - but the members with lower income should
have MORE allocation made to them.

 

[Harlan Grove]

I am trying to figure out how to do a weighted average using a reverse ratio
- that is, I have members of a group, each with Income, that I need to
allocate items among the group - but the members with lower income should
have MORE allocation made to them.

Let's say you have the following in A1:B6.

Person Income
A 1000
B 2000
C 3000
D 4000
E 6000

If you want to allocate $300 between them giving most to A and least
to E, you could try

A: =300/SUMPRODUCT(1/B$2:B$6)/B2
B: =300/SUMPRODUCT(1/B$2:B$6)/B3

etc. That is, use the reciprocals of income as the weights. You could
also use income raised to any negative power as the weights. That is,
(1/income)^x, where x < 0, is a decreasing function of income. For
example, using the square root of income,

A: =300/SUMPRODUCT(1/SQRT(B$2:B$6))/SQRT(B2)

which is equivalent to

A: =300*B2^-0.5/SUMPRODUCT(B$2:B$6^-0.5)