# HLOOKUP averaging blank cells

#### boolavka

##### New Member
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)))

Thx.

### 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
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
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
Platonic, I tried again, just added parenthesis around each hlookup and it worked. You are a genius! Thank you so much.

#### platonic567

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

Replies
1
Views
36
Replies
5
Views
70
Replies
3
Views
34
Replies
26
Views
2K
Replies
1
Views
36