| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I'm having a really strange problem with 3 if statements. | Excel Worksheet Functions | |||
| If Statement linked to cell with VLOOKUP problem - getting wrong v | Excel Worksheet Functions | |||
| Fundamental problem with IF statement | Excel Worksheet Functions | |||
| Formula Problem - If Statement | Excel Worksheet Functions | |||
| Excel Display Problem | Excel Discussion (Misc queries) | |||
Linear Mode
