Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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.

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,549
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default 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)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inverse wieghted average/ratio dtacotts Excel Worksheet Functions 1 June 12th 09 05:21 AM
Help with Weighted Average Kim Excel Discussion (Misc queries) 6 March 23rd 09 11:13 AM
weighted average elaine9412 Excel Worksheet Functions 6 August 28th 08 07:40 PM
weighted average inoexcel Excel Discussion (Misc queries) 3 May 1st 06 10:03 PM
Weighted Average hawsoon13 Excel Discussion (Misc queries) 2 August 26th 05 10:19 AM


All times are GMT +1. The time now is 12:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"