+ Reply to Thread
Results 1 to 6 of 6

Averages that include cells with IF statements

  1. #1
    hollenbaker
    Guest

    Averages that include cells with IF statements

    Is there no way around this? I have five numbers I want the average for.
    But they are not always greater than zero. To avoid DIV/0 errors those
    numbers have relatively long IF arguments. But that makes the Average -which
    also has IF arguments- exclude values of zero. I see that they are not
    actually values but not sure what to do about it. I am new to such complex
    authoring of functions and am at my whit's end on this. Someone please
    help!!!

    Some info:
    Cells I want to average contain:
    =IF(ISERROR(IF(RC[-2]<=0,"0",RC[-1]/RC[-2])),"0",RC[-1]/RC[-2])
    Formula I am using to find the average:
    =IF(ISERROR(AVERAGE(R[-5]C:R[-1]C)),"0",AVERAGE(R[-5]C:R[-1]C))



  2. #2
    Gary''s Student
    Guest

    RE: Averages that include cells with IF statements

    For individual numbers you are using zero's to avoid the error message.
    However AVERAGE() has been programmed to include zeros in its computation.
    AVERAGE() will exclude blanks. Try to have the IF() insert blanks rather
    than "0".

    Good Luck
    --
    Gary''s Student


    "hollenbaker" wrote:

    > Is there no way around this? I have five numbers I want the average for.
    > But they are not always greater than zero. To avoid DIV/0 errors those
    > numbers have relatively long IF arguments. But that makes the Average -which
    > also has IF arguments- exclude values of zero. I see that they are not
    > actually values but not sure what to do about it. I am new to such complex
    > authoring of functions and am at my whit's end on this. Someone please
    > help!!!
    >
    > Some info:
    > Cells I want to average contain:
    > =IF(ISERROR(IF(RC[-2]<=0,"0",RC[-1]/RC[-2])),"0",RC[-1]/RC[-2])
    > Formula I am using to find the average:
    > =IF(ISERROR(AVERAGE(R[-5]C:R[-1]C)),"0",AVERAGE(R[-5]C:R[-1]C))
    >
    >


  3. #3
    hollenbaker
    Guest

    RE: Averages that include cells with IF statements

    That is not going to work. I WANT the zeros included it the average. When I
    view the spread sheet, I see zeros in the cells, however it seems that the
    formula sees the other formula rather than the value that is displayed.

    These are production numbers and time spent with no production is still time
    on the clock.

    "Gary''s Student" wrote:

    > For individual numbers you are using zero's to avoid the error message.
    > However AVERAGE() has been programmed to include zeros in its computation.
    > AVERAGE() will exclude blanks. Try to have the IF() insert blanks rather
    > than "0".
    >
    > Good Luck
    > --
    > Gary''s Student
    >
    >
    > "hollenbaker" wrote:
    >
    > > Is there no way around this? I have five numbers I want the average for.
    > > But they are not always greater than zero. To avoid DIV/0 errors those
    > > numbers have relatively long IF arguments. But that makes the Average -which
    > > also has IF arguments- exclude values of zero. I see that they are not
    > > actually values but not sure what to do about it. I am new to such complex
    > > authoring of functions and am at my whit's end on this. Someone please
    > > help!!!
    > >
    > > Some info:
    > > Cells I want to average contain:
    > > =IF(ISERROR(IF(RC[-2]<=0,"0",RC[-1]/RC[-2])),"0",RC[-1]/RC[-2])
    > > Formula I am using to find the average:
    > > =IF(ISERROR(AVERAGE(R[-5]C:R[-1]C)),"0",AVERAGE(R[-5]C:R[-1]C))
    > >
    > >


  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,743
    See if this helps. Drop the quotes from around the 0's (...,0,...). With the quotes in place, the IF function returns the TEXT "0" which AVERAGE ignores because it's text. Without the quotes, the IF function will return the number 0 which AVERAGE will acknowledge and include in your average.

  5. #5
    hollenbaker
    Guest

    Re: Averages that include cells with IF statements

    YOU ROCK!!

    So simple yet sooo far from reach. Days saved. Thank you.

    "MrShorty" wrote:

    >
    > See if this helps. Drop the quotes from around the 0's (...,0,...).
    > With the quotes in place, the IF function returns the TEXT "0" which
    > AVERAGE ignores because it's text. Without the quotes, the IF function
    > will return the number 0 which AVERAGE will acknowledge and include in
    > your average.
    >
    >
    > --
    > MrShorty
    > ------------------------------------------------------------------------
    > MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
    > View this thread: http://www.excelforum.com/showthread...hreadid=468863
    >
    >


  6. #6
    lschuh
    Guest

    Re: Averages that include cells with IF statements

    I have a similiar problem whereas the last part of my formula is not
    averaging but summing my formula: I have
    {=average)if(iserr(j10:l10<>0),0,average(j10:l10)))}

    what was working was the {=average(if(j10:l10<>0,j10:l10,""))} but what i
    get in cells with 0 is the #div/ error.

    "MrShorty" wrote:

    >
    > See if this helps. Drop the quotes from around the 0's (...,0,...).
    > With the quotes in place, the IF function returns the TEXT "0" which
    > AVERAGE ignores because it's text. Without the quotes, the IF function
    > will return the number 0 which AVERAGE will acknowledge and include in
    > your average.
    >
    >
    > --
    > MrShorty
    > ------------------------------------------------------------------------
    > MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
    > View this thread: http://www.excelforum.com/showthread...hreadid=468863
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1