# Excel Averageif across range of cells including blanks

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### synkan

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

#### Zack Barresse

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

#### synkan

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
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

#### Zack Barresse

Can you post a subset of your data?

#### synkan

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

#### Attachments

• 60.6 KB Views: 41

#### Zack Barresse

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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On