HLOOKUP averaging blank cells

[boolavka]

Hi,


I am trying to calculate the average of values returned by hlookup. The problem is that some of dataranges have columns with blank cells. For some reason excel counts those blank cells and skews my average.

=AVERAGE(HLOOKUP(C3,data_range1,3,FALSE),(HLOOKUP(C3,data_range2,3,FALSE)),(HLOOKUP(C3,data_range3,3,FALSE)))

Please help me to figure out how to go about it w/o complicating too much.

Thx.

 

[platonic567]

Try this:

=(HLOOKUP(C3,data_range1,3,0)+HLOOKUP(C3,data_range2,3,0)+HLOOKUP(C3,data_range3,3,0))/(HLOOKUP(C3,data_range1,3,0)<>0+HLOOKUP(C3,data_range2,3,0)<>0,HLOOKUP(C3,data_range3,3,0)<>0)

 

[boolavka]

Try this:

=(HLOOKUP(C3,data_range1,3,0)+HLOOKUP(C3,data_range2,3,0)+HLOOKUP(C3,data_range3,3,0))/(HLOOKUP(C3,data_range1,3,0)<>0+HLOOKUP(C3,data_range2,3,0)<>0,HLOOKUP(C3,data_range3,3,0)<>0)

Thank you.

Nope, unfortunately doesn`t work.

 

[boolavka]

Platonic, I tried again, just added parenthesis around each hlookup and it worked. You are a genius! Thank you so much.

 

[platonic567]

You're welcome! Guess I should never underestimate the importance of parenthesis!