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.

Average If and #DIV/0!

Discussion in 'Business Applications' started by laigUT, Oct 25, 2011.

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

    laigUT Thread Starter

    Joined:
    Oct 25, 2011
    Messages:
    3
    In my sheet I am using conditional formating to return 4-color traffic lights (using number 0-3). At the bottom of each colum I want the average of all number 1-3, excluding any 0s. I am using the formula =AVERAGEIFS(I3:I8,I3:I8,"<=3",I3:I8,">=1").

    This is providing the correct answer unless it is a column of all 0s. How can I have the current formula I am using but also if the answer is #DIV/0! for it to return a zero instead of the error message?

    An example of what I am doing is attached. Thanks for your help.
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Check out the ISERROR function. You can use it inside of another IF statement.

    Rollin
     
  3. laigUT

    laigUT Thread Starter

    Joined:
    Oct 25, 2011
    Messages:
    3
    Where would the ISERROR function be inserted into an AVERAGEIF function? Or do I need to use a normal IF function?

    I am basically trying to say average I3:I8 if the values are <=3 and >=1. If those statements are not true then return a value of 0.
     
  4. laigUT

    laigUT Thread Starter

    Joined:
    Oct 25, 2011
    Messages:
    3
    The other formula I tried was:
    =IF(AND(E3:E8<=3,E3:E8>=1),AVERAGE(E3:E8),"0")

    But this only returned the VALUE error?

    Thank you so much for your help! I am trying to teach myself how to use Excel with only moderate success so far.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,552
    First Name:
    Hans
    =IF(ISERROR(AVERAGE(E3:E8)),0,AVERAGE(E3:E8))

    This is the way ISERROR() works
     
  6. 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!

Thread Status:
Not open for further replies.

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

  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