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

[johnwspears]

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.

[Domenic]

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!

[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!

[pikapika13]

Does this not work??
=AVERAGE(IF(C9:C15<>0, C9:C15,""))

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

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!

[Domenic]

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?