Conditional Formula

[Mike]

I have a worksheet that takes the average of a set number
of cells. The spreadsheet asks questions yes, no, or x
and then gives an average above. If I have one area that
has all x's listed it gives me #Div/0!. What formula can
I write that will calculate the average when there is a 5
or 0 in the cells but if all cells in the range are x's it
avoids the #div/0!

 

[JulieD]

Hi Mike

try the AVERAGEA function instead of the average function, but check that it
gives you the answers you are looking for in all situations.

Regards
JulieD

 

[Mike]

Here is the formula I am trying to use (which is not
working). I cannot use the Avergea as it gives a result
of 0 and then calculates into my over all average (there
are many areas to the spreadsheet). Help!

=IF(C22:C23="x","x",AVERAGE(C22:C23))

 

[JulieD]

Hi Mike

how about
=IF(ISERROR(AVERAGE(C22:C23)),"x",AVERAGE(C22:C23))
this will return x if the avereage function returns any sort or error.

Regards
JulieD