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

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

Could someone guide me please????
 
Joined
Jul 25, 2004
Messages
5,458
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

Thread Starter
Joined
Nov 12, 2011
Messages
3
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
 

synkan

Thread Starter
Joined
Nov 12, 2011
Messages
3
Sample file uploaded
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

Joined
Jul 25, 2004
Messages
5,458
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top