ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Problem with IF statement (https://www.excelbanter.com/excel-worksheet-functions/120035-problem-if-statement.html)

Connie Martin
Problem with IF statement
 
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
Problem with IF statement
 
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" wrote:

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

Connie Martin
Problem with IF statement
 
No, sorry. I still get #VALUE!

"Ron Coderre" wrote:

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" wrote:

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

driller
Problem with IF statement
 
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...
on BA4
=if(or(AX4="",AQ4=""),"",IF(AX4<AQ4,"chk.dates?",A X4-AQ4-AU14))



"Connie Martin" wrote:

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

Teethless mama
Problem with IF statement
 
=IF(OR(AQ4="",AX4="",AU4=""),"",AX4-AQ4-AU4)

Format cell as General



"Connie Martin" wrote:

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

Arvi Laanemets
Problem with IF statement
 
Hi

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

Arvi Laanemets


"Connie Martin" wrote in message
...
No, sorry. I still get #VALUE!

"Ron Coderre" wrote:

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" wrote:

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
Problem with IF statement
 
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" wrote:

No, sorry. I still get #VALUE!

"Ron Coderre" wrote:

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" wrote:

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

Connie Martin
Problem with IF statement
 
I still get #VALUE!

"Arvi Laanemets" wrote:

Hi

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

Arvi Laanemets


"Connie Martin" wrote in message
...
No, sorry. I still get #VALUE!

"Ron Coderre" wrote:

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" wrote:

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

Connie Martin
Problem with IF statement
 
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" wrote:

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...
on BA4
=if(or(AX4="",AQ4=""),"",IF(AX4<AQ4,"chk.dates?",A X4-AQ4-AU14))



"Connie Martin" wrote:

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

Connie Martin
Problem with IF statement
 
Bingo!! This one works. Thank you!

"Teethless mama" wrote:

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

Format cell as General



"Connie Martin" wrote:

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

driller
Problem with IF statement
 
thanks for the encouraging remarks..good luck...

"Connie Martin" wrote:

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" wrote:

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...
on BA4
=if(or(AX4="",AQ4=""),"",IF(AX4<AQ4,"chk.dates?",A X4-AQ4-AU14))



"Connie Martin" wrote:

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

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

"Connie Martin" wrote:

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" wrote:

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...
on BA4
=if(or(AX4="",AQ4=""),"",IF(AX4<AQ4,"chk.dates?",A X4-AQ4-AU14))



"Connie Martin" wrote:

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


All times are GMT +1. The time now is 08:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com