Problem with IF statement

[Connie Martin]

In BA4 I have this: =IF(AX4=0,"",AX4-AQ4)-AU4 is giving me #VALUE! when all
cells are blank. If all cells have data, I get the right answer. AX and AQ
columns are date format, whereas AU is number format. Is that my problem?
Here's one that works, with BA being the answer:

Col. AQ Col. AX Col. AU Col. BA
11/22/2006 12/5/2006 14 -1

Thank you
Connie

 

[Ron Coderre]

The problem lies in the IF statement
=IF(AX4=0,"",AX4-AQ4)-AU4

When AX4=0, the IF statement returns "".
That's TEXT....so ""-AU4 returns an error.
(It's like trying to use: "DOG"-AU4)

try this:
=IF(AX4=0,0,AX4-AQ4)-AU4

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Connie Martin]

No, sorry. I still get #VALUE!

The problem lies in the IF statement
=IF(AX4=0,"",AX4-AQ4)-AU4

When AX4=0, the IF statement returns "".
That's TEXT....so ""-AU4 returns an error.
(It's like trying to use: "DOG"-AU4)

try this:
=IF(AX4=0,0,AX4-AQ4)-AU4

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[driller]

Hi. connie,
thats good thing about If statement, it shows you the problem on a formula..
try this
scenario 1 : if you like to have the date on column AX to always be greater
than the date in column AQ...so as logic will remain...

 

[Teethless mama]

=IF(OR(AQ4="",AX4="",AU4=""),"",AX4-AQ4-AU4)

Format cell as General

 

[Arvi Laanemets]

Hi

Try:
=SUM(AX4,-AQ4*(AX4<>0),-AU4)

Arvi Laanemets

 

[Ron Coderre]

The likely reason for the #VALUE! error is a text value in one of the
referenced cells.
Example: if AQ4 contained " 11/22/2006 "

One other thought...
Could it be that your really want this formula?

=IF(AX4=0,"",(AX4-AQ4-AU4))
Where, if AX4=0 then...don't perform the calculation

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Connie Martin]

I still get #VALUE!

Hi

Try:
=SUM(AX4,-AQ4*(AX4<>0),-AU4)

Arvi Laanemets

 

[Connie Martin]

I thought this one was going to work because the rows where there was no
data, the #VALUE! disappeared with this formula, but where there was data I
now get a #VALUE! instead of the correct answer that I was getting!

 

[Connie Martin]

Bingo!! This one works. Thank you!

=IF(OR(AQ4="",AX4="",AU4=""),"",AX4-AQ4-AU4)

Format cell as General

 

[driller]

thanks for the encouraging remarks..good luck...

I thought this one was going to work because the rows where there was no
data, the #VALUE! disappeared with this formula, but where there was data I
now get a #VALUE! instead of the correct answer that I was getting!

 

[driller]

opppsss.. try a typo correction - my mistake :
read the formula at the end...
its AU4 not AU14....sorry for the wrong spoonfeed...