Average vlookup exclude zero/blanks

[EB1217]

Hello. I am trying to find an average of four points within a table on a different sheet; Working in Sheet1, referencing Table1 on Sheet2, has four points (B1,D1,F1,G1) that I would like to know the average of. Two points have numbers while two points are blank. In using an average formula and a vlookup formula with A1 on both sheets that is the look-up value, the average is treating the blanks as zeros which in return lowers the average. Anyone have any suggestions?

Thank you in advance.

 

[davidspier]

I think you need an averageif formula.

Using those cell references you mentioned, the formula would be something like:

=AVERAGEIF(B1:G1,"<>0",B1:G1)

 

[Momentman]

Averageif should do it, in the criteria range, put "<>"" " to indicate not blank or"<>0" to indicate not zero