Averaging with a conditional check

[sarah]

Hi,




I am trying to figure out an efficient formula for a weighted average of a long series of numbers:

([Mercer #1: Weighting]*[Mercer 1 Base 25th %ile]) + ([Mercer #2: Weighting]*[Mercer 2 Base 25th %ile])+……)/([Mercer #1: Weighting]+[Mercer #2: Weighting]+………)


The problem is, there are null values for many of the “Base 25%ile” numbers and therefore the weighted average does not calculate unless there are values for all the “Base 25%ile” fields. Ideally, the formula would just average the fields in which there are numerical values and ignore the rest. Is there an efficient way to rewrite the above formula? My first thought is to do a isNull check for every “base 25 %ile” but this formula would get very complicated and long since there are many fields we would have to check for. Any advice would be greatly appreciated!!


Thank you!
Sarah

[dcrake]

Are all your base fields in a single table?

Mercer 1 Base 25th %ile
Mercer 2 Base 25th %ile
Mercer 3 Base 25th %ile

etc.

If so, this is not the correct way to do this. Your database tables need normalising.

In the mean time you could create a union query to normalise them

Select ID, [Mercer 1 Base 25th %ile] As Base, 1 As Cnt From Table Where [Mercer 1 Base 25th %ile] Is Not Null

Union

Select ID, [Mercer 2 Base 25th %ile] As Base, 1 As Cnt From Table Where [Mercer 2 Base 25th %ile] Is Not Null

Union

Select ID, [Mercer 3 Base 25th %ile] As Base, 1 As Cnt From Table Where [Mercer 3 Base 25th %ile] Is Not Null

Etc

This will then give you a list of all base fields with values (Exludes Null Entries) Then this will give you a more weighted average.

If you then group by ID and base your calulation on the base / sum (cnt) to tell you how many have values. I Think.

David