Getting #VALUE Result in a Simple Excel Formula
I know what is wrong. I just do not know how to fix it.
B2, contains a number
C2, contains a number
D2, subtracts, B2-C2 and puts the result in D2, IF B2>0 and C2>0, otherwise D2 contains " "
F2, contains a number
G2, contains a number
H2, subtracts, F2-G2 and puts the result in H2, IF F2>0 and G2>0, otherwise H2 contains " "
At this point, everything works fine!
J2 subtracts D2-H2, IF D2>0 and H2>0, otherwise should put " " in H2
THAT WORKS, AS LONG AS D2 AND H2 CONTAIN A NUMBER, BUT
If B2 or C2 or F2 or G2 does not contain a number, J2 Returns #VALUE
Obviously I need to store something besides " " in F2 and H2 when B2 or C2 or F2 or G2 is blank.
Sample file attached.
EE-IFAND-SAMPLE.xlsx
B2, contains a number
C2, contains a number
D2, subtracts, B2-C2 and puts the result in D2, IF B2>0 and C2>0, otherwise D2 contains " "
F2, contains a number
G2, contains a number
H2, subtracts, F2-G2 and puts the result in H2, IF F2>0 and G2>0, otherwise H2 contains " "
At this point, everything works fine!
J2 subtracts D2-H2, IF D2>0 and H2>0, otherwise should put " " in H2
THAT WORKS, AS LONG AS D2 AND H2 CONTAIN A NUMBER, BUT
If B2 or C2 or F2 or G2 does not contain a number, J2 Returns #VALUE
Obviously I need to store something besides " " in F2 and H2 when B2 or C2 or F2 or G2 is blank.
Sample file attached.
EE-IFAND-SAMPLE.xlsx
ASKER
I am uploading "working" sample and leaving the question open so someone can tell me how to accomplish the solution with conditional formatting. What I was looking for to start with was to avoid getting a - in the field, H2 and H3 in the accompanying sample.
EE-IFAND-SAMPLE.xlsx
EE-IFAND-SAMPLE.xlsx
To remove the hyphen
select the cells
press Ctrl-1
Select the Number tab
From Category select Custom
In the Type box get rid of the hyphen
select the cells
press Ctrl-1
Select the Number tab
From Category select Custom
In the Type box get rid of the hyphen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sktneer, worked wonderfully and allows me to have or not to have $
Sorry Syed. I got the shift cells up, etc. when I entered Ctrl 1
Sorry Syed. I got the shift cells up, etc. when I entered Ctrl 1
Glad to know that Bill!
ASKER
Syed. I got the shift cells up, etc. when I entered Ctrl -1
Got it, sort of. You meant for me to enter Ctrl and the number 1, not on the keypad.
Then I got the custom format window.
Sorry, yours was a solution as well.
I will ask the moderator to split the points. If not, I will make it up to you!
Got it, sort of. You meant for me to enter Ctrl and the number 1, not on the keypad.
Then I got the custom format window.
Sorry, yours was a solution as well.
I will ask the moderator to split the points. If not, I will make it up to you!
Don't you worry, It is good you have a solution.
ASKER
Is this when you feel really smart ? ...or really dumb when you realize the solution?