inverse weighted average
 

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.

Answer: inverse weighted average
 

To calculate an inverse weighted average, follow these steps:

1. Determine the weights: In this case, the weights will be the inverse of the income for each member. To calculate the inverse, you can divide 1 by the income for each member. For example, if one member has an income of $10,000, their weight would be 1/10,000 = 0.0001.
2. Calculate the weighted values: Multiply each item by the weight for the corresponding member. For example, if you have an item worth $100 and the member with an income of $10,000 has a weight of 0.0001, their weighted value for that item would be $100 * 0.0001 = $0.01.
3. Sum the weighted values: Add up all the weighted values for each member to get the total weighted value.
4. Sum the weights: Add up all the weights for each member to get the total weight.
5. Calculate the inverse weighted average: Divide the total weighted value by the total weight. This will give you the inverse weighted average.

For example, let's say you have three members with incomes of $10,000, $20,000, and $30,000, and you want to allocate an item worth $100 among them. The weights would be 0.0001, 0.00005, and 0.000033, respectively. The weighted values for the item would be $0.01, $0.005, and $0.0033, respectively. The total weighted value would be $0.0183, and the total weight would be 0.000183. Dividing the total weighted value by the total weight gives an inverse weighted average of $100.

inverse weighted average
 

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"

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.

inverse weighted average
 

Dan Cotts wrote...
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)