# Excel Averageif across range of cells including blanks

Discussion in 'Business Applications' started by synkan, Nov 12, 2011.

Not open for further replies.

Joined:
Nov 12, 2011
Messages:
3
I am using the formula below to calculate averages when a particular value matches data in another column

=AVERAGEIF(\$A\$4:\$A\$279,\$O19,\$L\$4:\$L\$279)

Problem is if the range \$L\$4:\$L\$279 has some blank cells, the formula throws up #DIV/0!

I wish to tell excel to not throw up that value and put blank in the results cell if it comes across blanks in the tested range.

I am using Excel 2007

2. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi there, welcome to the board!

Sounds like you may have zeroes in your numerical range to average. You can utilize the AVERAGEIFS() function to house multiple criteria to use the function on. Here is one way to exclude zero values from the average range...

Code:
`=AVERAGEIFS(\$L\$4:\$L\$279,\$A\$4:\$A\$279,\$O19,\$L\$4:\$L\$279,"<>"&0)`
HTH

Joined:
Nov 12, 2011
Messages:
3
Thanks Zack but it does not seem to do the trick, I still get #DIV/0! showing up in the results cell.
Am I missing out on specifying some kind of criteria or do we need to try some kind of nested sequence?

Regards

Tapas

4. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Can you post a subset of your data?

Joined:
Nov 12, 2011
Messages:
3
Maybe this helps more. So far I have been manually deleting the data where it throws an error. I want the cells to show blanks if the result is null when making the average

File size:
60.6 KB
Views:
41
6. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Ah, so there is not data in the range, I thought you were talking about values in the average_range. You can utilize the IFERROR() function...

Shift A:
Code:
`=IFERROR(AVERAGEIF(\$A\$4:\$A\$279,\$O4,\$D\$4:\$D\$279),"")`
Shift B:
Code:
`=IFERROR(AVERAGEIF(\$A\$4:\$A\$279,\$O4,\$H\$4:\$H\$279),"")`
Shift C:
Code:
`=IFERROR(AVERAGEIF(\$A\$4:\$A\$279,\$O4,\$L\$4:\$L\$279),"")`
Combined:
Code:
`=IFERROR(AVERAGE(X4,U4,R4),"")`
HTH

As Seen On