Averages of nonconsecutive cells including zeros.

Hello all,
I am trying to average cells E2 and G2 and if one is a zero factor that into the formula. The formula is in I2 and the formula must exclude whatever is in F2 and H2.

I have tried a plethora of formulas and I cannot get one to work. I think I have spent too long trying to fix it so some fresh ideas would be greatly received. I also note that the formulas I have tried have returned a DIV0! Error in blank cells.

Many thanks,

PER11

try:

Please Login or Register  to view this content.

Thank you for all your help.

The formula I ended up with was

Formula:

Please Login or Register  to view this content.

. The average E2,G2 function returned a divide by 0 error.

How about trying the formulas that were actually suggested?

like
=(E2+G2)/2
and
=AVERAGE(E2,G2)

If your formula returned #Div/0!, that suggests that this part resulted in 0
((E2<>0)+(G2<>0))

And that would suggest that both E2 and G2 are either 0 or Blank.

To handle that issue, to return "" when those cells are blank, try iferror
=IFERROR(AVERAGE(E2,G2),"")
or
=IFERROR((E2+G2)/2,"")

I think this part of your original post has confused everyone.

I am trying to average cells E2 and G2 and if one is a zero factor that into the formula.

The way that reads, it means you want 0's to be INCLUDED in the average.
So an average of say 4 and 0 would be 2... (4+0)/2


Did you really mean that 0's should NOT be included in the average?
So an average of 4 and 0 would be 4... (4+0)/1


And also this part

must exclude whatever is in F2 and H2

What exactly is in F2 and H2 ? If it's NOT numeric, then an average function would ignore them by design.
If they ARE numeric, then is there anything else that can be used as a key to determine which cells to include (or exclude) in the average?
For example, a specific Header Text string. So we could say something like Average E2:G2 IF the Text in E1:G1 = "x" (or whatever) ?


Perhaps you could attach a sample file?