Results 1 to 2 of 2
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    New York, New York, USA
    Thanked 0 Times in 0 Posts

    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?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    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:

    Or if you want to eliminate just the lowest value:

    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.


Posting Permissions

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