1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Averageif across range of cells including blanks

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

Thread Status:
Not open for further replies.
Advertisement
  1. synkan

    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????
     
  2. Zack Barresse

    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
     
  3. synkan

    synkan Thread Starter

    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

    Zack Barresse

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

    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
     

    Attached Files:

  6. Zack Barresse

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

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026518

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice