HLOOKUP averaging blank cells

boolavka

New Member
Joined
Oct 1, 2014
Messages
3
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
Try this:

Code:
=(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

New Member
Joined
Oct 1, 2014
Messages
3
Try this:

Code:
=(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

New Member
Joined
Oct 1, 2014
Messages
3
Platonic, I tried again, just added parenthesis around each hlookup and it worked. You are a genius! Thank you so much.
 

platonic567

Board Regular
Joined
Feb 21, 2014
Messages
153
You're welcome! Guess I should never underestimate the importance of parenthesis!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,709
Messages
5,524,433
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top