1. Weighted Averages (XL XP)

Hi All,

I wanted to get peoples input into how to create a formula that creates weighted averages. Sounds easy and it is (see example) however, I wanted to make it flexible by giving the user some method of included / excluding selected values from the average process (either high / low or specifically marked values).

I've set up an example of what I am talking about. I wanted to calculate a weighted average but exclude the 1992 year (P2/P1 factor of 1.357). I had to specifically modify the formula to get it to work. What I would like to do is develop a formula that automatically excludes any (say) bold years. So if I change the bold year from 1992 to 1997 then the formula would automatically recalculate the weighted average based on the new selections.

Any suggestions?

2. Re: Weighted Averages (XL XP)

If you want to test for "BOLD" you will have to use a user defined function, excel has no builtin function to test for boldness.

If you want to eliminate multiple values based on a criteria you can use something like:
=SUMIF(E4:E14,">1.5",C4:C14)/SUMIF(E4:E14,">1.5",B4:B14)

Or if you want to eliminate just the lowest value:
=SUMIF(E4:E14,">"&MIN(E4:E14),C4:C14)/SUMIF(E4:E14,">"&MIN(E4:E14),B4:B14)

You could also eliminate the max or you could off course eliminate multiple small numbers (or large) using the SMALL or LARGE functions instead of MIN.

More complex criteria (ie multiple ones) would require an array. You could also use D-Functions if you created a criteria.

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•