Weighted averages

  • Thread starter Thread starter Djembe
  • Start date Start date
D

Djembe

How does Excel handle weighted averages? For example, if you have two
populations of different sizes who have each responded to 100
different questions and you want to determine the group mean (so you
obviously weight them based on population size).

My problem is that if I break it down so that each cell is multipled
by its relative % size (eg 55% and the other 45%), it doesn't factor
in the scenario whereby a response is left blank - Excel treats it as
zero and would multiple it by 55% rather than keeping it the same
value.

Many thanks in advance for any assistance.
 
The problem with sumproduct()/sum() method is that it will give errors
for blank values.

It is akin to the average() function in excel. If you have a list of
10 possible values, and one of them is blank, average() only divides
by 9.

The above method will not recognise when there is a blank and adjust
accordingly.
 
Back
Top