+ Reply to Thread
Results 1 to 5 of 5

IF-then statement w/ an average and a #DIV/0! error

  1. #1
    Registered User
    Join Date
    04-12-2005
    Posts
    2

    IF-then statement w/ an average and a #DIV/0! error

    I have one cell (G17) that takes the overall average of four yearly averages (C9, C11, C13, C15). However, if one of the years (C9, C11, C13, C15) does not include data then it is marked as #DIV/0!. How can I calculate G17 without having to manually delete the (C9, C11, C13, C15) cell that contains the #DIV/0! error? I'm sure the solution is an IF-then statement but I'm having a hard time executing it.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Yes, you can include an IF statement for each formula in each of the cells (C9, C11, C13, C15), something like...

    =IF(COUNTIF(Range,">0"),AVERAGE(Range),"")

    Hope this helps!

  3. #3
    Registered User
    Join Date
    04-12-2005
    Posts
    2

    IF-then statement w/ an average and a #DIV/0! error

    Thank you so much for your reply. I really tried your statement but could not get that to work. As another attempt I took your advice and tried several IF statements. Here is my attempt at the four statements but I am having trouble combining them in to one overall statement for one cell in Excel. Again, what I'm try to do is average a set of four averages. The set will not always be the same number of averages to average.

    In other words, if the set is one average the I just want to use that average and not try and calculate an average of the remaining three #DIV/0! errors. If it is two averages then I want to average those two averages, and not the other two #DIV/0! errors. If it is three averages then I want to average those three averages, and not the other one #DIV/0! errors.

    =IF(C9>0,C9,"")
    =IF((AND(C9>0,C11>0)),AVERAGE(C9,C11),C9)
    =IF((AND(C9>0,C11>0,C13>0)),AVERAGE(C9,C11,C13),AVERAGE(C9,C11)
    =IF((AND(C9>0,C11>0,C13>0,C15>0)),AVERAGE(C9,C11,C13,C15),AVERAGE(C9,C11,C13)))

    THANK YOU!

  4. #4
    Forum Contributor
    Join Date
    06-21-2004
    Posts
    106
    Does this not work??
    =AVERAGE(IF(C9:C15<>0, C9:C15,""))

    Or do you have cells in between that you don't want calculated?

    Quote Originally Posted by johnwspears
    Thank you so much for your reply. I really tried your statement but could not get that to work. As another attempt I took your advice and tried several IF statements. Here is my attempt at the four statements but I am having trouble combining them in to one overall statement for one cell in Excel. Again, what I'm try to do is average a set of four averages. The set will not always be the same number of averages to average.

    In other words, if the set is one average the I just want to use that average and not try and calculate an average of the remaining three #DIV/0! errors. If it is two averages then I want to average those two averages, and not the other two #DIV/0! errors. If it is three averages then I want to average those three averages, and not the other one #DIV/0! errors.

    =IF(C9>0,C9,"")
    =IF((AND(C9>0,C11>0)),AVERAGE(C9,C11),C9)
    =IF((AND(C9>0,C11>0,C13>0)),AVERAGE(C9,C11,C13),AVERAGE(C9,C11)
    =IF((AND(C9>0,C11>0,C13>0,C15>0)),AVERAGE(C9,C11,C13,C15),AVERAGE(C9,C11,C13)))

    THANK YOU!

  5. #5
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Actually, my suggestion is to replace the formulas you have in C9, C11, C13, and C15, with a formula that returns a null value (leaves the cell blank) if no data exists to average for that particular year. The formula I offered previously would do that.

    Then, you can simply use...

    =AVERAGE(C9,C11,C13,C15)

    ...for your overall average.

    What formulas do you have in C9, C11, C13, and C15?

+ 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