Calculating Weighted Averages

[HRPRGMNGT]

Hi

I have the formula below to calculate the average scores for each year (the one below is for 2012 weighted average)

=sumproduct($B$2:$E$2,$B3:$E3)/sum($B$2:$E$2)

however in some of the questions were not asked so that the score for that year is blank, how do I modify the above formula to exclude those scores and the employee surveyed count for the 'empty' year. So just to clarify in my example below I want to include all in Q1, however I want to exclude 2009 from my Q2 weighted average and for my Q3 weighted average I want to exclude 2010

Thanks
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]col B
[/TD]
[TD]col C
[/TD]
[TD]col D
[/TD]
[TD]col E
[/TD]
[/TR]
[TR]
[TD]survey year
[/TD]
[TD]2009
[/TD]
[TD]2010
[/TD]
[TD]2011
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]employees surveyed
[/TD]
[TD]125
[/TD]
[TD]175
[/TD]
[TD]230
[/TD]
[TD]550
[/TD]
[/TR]
[TR]
[TD]Q1
[/TD]
[TD]63%
[/TD]
[TD]72%
[/TD]
[TD]75%
[/TD]
[TD]85%
[/TD]
[/TR]
[TR]
[TD]Q2
[/TD]
[TD][/TD]
[TD]65%
[/TD]
[TD]72%
[/TD]
[TD]71%
[/TD]
[/TR]
[TR]
[TD]Q3
[/TD]
[TD]72%
[/TD]
[TD][/TD]
[TD]64%
[/TD]
[TD]75%
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[JasC]

try something like

=SUMPRODUCT($B$2:$E$2,$B3:$E3)/SUMIF($B3:$E3,">0",$B$2:$E$2)

OR

=SUMPRODUCT($B$2:$E$2,$B3:$E3)/SUMPRODUCT($B$2:$E$2*($B3:$E3>0))