What is the formula for a weighted average with cells possibly containing 0s

[aschenk21]

Hello,

Please assist i am trying to write a formula that will take that weight average of the of cells and not count any cells that have a "0" in them.
my data set is;

count average
8 6.832638889
12 5.388657407
10 6.961319444
2 13.83506944
0 0
0 0
2 9.364583333
0 0
3 4.240740741
1 9.833333333
1 2.885416667
2 10.953125
2 13.88888889
0 0


my current formula that i thought to use is =SUMPRODUCT(AR291:AR303)/COUNTIF(AQ291:AQ303,">0"). however, the number i need to get is 7.22005814

 

[steve the fish]

Hi. Try this:

=SUMPRODUCT(AQ291:AQ303*AR291:AR303)/SUM(AQ291:AQ303)

 

[aschenk21]

Thank you Steve!!

Hi. Try this:

=SUMPRODUCT(AQ291:AQ303*AR291:AR303)/SUM(AQ291:AQ303)