Or Formula

[Tami]

I have a formula I'm trying to write but I'm having
difficulty. I have several columns that if they are
blank, I want the end result to be blank.
This is what I'm trying to write but I keep getting a
#value error:

If(or(A1="",D1="",F1="",M1="",R1="",Z1=""),"",
average(A1,D1,F1,M1,R1,Z1)
I'm sure this is something simple but I can't figure it
out. HELP?

Thanks in advance,
Tami

 

[Frank Kabel]

Hi
is there anything else in A1, D1, etc (as besides a missing bracket at
then end the formula looks o.k). Maybe there is a psace within one of
the cells. Try checking the cells with the formula
=ISTEXT(A1)
this should return fALSE for all cells

 

[JE McGimpsey]

One way:


=IF(COUNT(A1,D1,F1,M1,R1,Z1),AVERAGE(A1,D1,F1,M1,R1,Z1),0)



It's easier if you name your range:

Select A1,D1,F1,M1,R1 and Z1 and enter a name, say "myrng" (without
quotes) in the Name box (at the left of the Formula Bar).

Then you can enter:

=IF(COUNT(myrng),AVERAGE(myrng),0)

 

[JE McGimpsey]

Left out the test for all having numbers:

=IF(COUNT(A1,D1,F1,M1,R1,Z1)=6,AVERAGE(A1,D1,F1,M1,R1,Z1),0)