Trouble with IF THEN ELSE formula

  • Thread starter Thread starter MPSr
  • Start date Start date
M

MPSr

I know what I want the formula to do but cannot create the end result.
Here's what I hope you can solve.

The results are to show in cell N16. If cell J13 is >0 then I want N16
to check cell B16 for "x" (text), if B16 is marked with an "x" then
N16 is to show $1000, but if B16 is blank then N16 is to show N/A.

I'm not sure that I should even use the IF THEN ELSE formula in this
instance. Any help will be appreciated!
 
One way:

Since you didn't specify, I'm assuming that N16 should show a null
string if J13<=0, right?

N16: =IF(J13>0,IF(B16="x",1000,"N/A"),"")

Format N16 as $0



Format N16 as currency.
 
Thanks for the response. I knew there was an easy way to
do this but after staring at it for so long I just
couldn't come up with the solution.

Now, there's one thing I should have originally stated in
my problem. I think I should have said if J13 is >0 but
not equal to 0. This way if J13 shows 0, N16 will show
N/A.

Hope you can help.
 
MPSr,
I think I should have said if J13 is >0 but
not equal to 0.
Click to expand...
You did say that in your original message.
This way if J13 shows 0, N16 will show N/A.
Click to expand...
The formulas you were given do exactly that.

The "AND" statement in the formula means that
both cases have to be true for the 1000 to appear.
J13 "has" to be greater than 0 while at the same time
b16 equals "x".

John
 
For some reason when J13 shows 0 but B16="x" the 1000 is
showing in N16. I've re-typed the formula as you had
originally sent and it still showing the end result in
N16 as 1000. Any ideas?
-----Original Message-----
MPSr,

You did say that in your original message.

The formulas you were given do exactly that.

The "AND" statement in the formula means that
both cases have to be true for the 1000 to appear.
J13 "has" to be greater than 0 while at the same time
b16 equals "x".

John
Click to expand...
0 then I want N16 to check cell B16 for "x" (text), if
B16 is marked with an "x" then N16 is to show $1000, but
if B16 is blank then N16 is to show N/A.formula in this instance. Any help will be appreciated!
 
MPSr,
IF J13 is >0
Click to expand...
means if J13 is numerically greater than zero or any other character
including a space.

Another example.....
Say that J13 has the following formula:
=IF(B1>0,A1/B1,"")
With teh above, if B1 is not greater than zero, return a blank string.
That blank string will evaluate to being greater than zero.
To correct the formula to work with the formula in N16, you could use
=IF(B1>0,A1/B1,0)

If you still can't get it to work, post the formula that you have in
J13.

One more option.....
Add a third argument to the AND statement:
=IF(AND(ISNUMBER(J13),J13>0,B16="X"),1000,"N/A")

The above will be true only if all of the following are true.
What's in J13 is a number, that number is greater than 0
and B16 = "X"

John
 
Adding the third argument worked like a charm. FYI, J13
did have a formula, =if(iserror(j12/j11),"0",(j12/j11))

Thanks for your help!
-----Original Message-----
MPSr,

means if J13 is numerically greater than zero or any other character
including a space.

Another example.....
Say that J13 has the following formula:
=IF(B1>0,A1/B1,"")
With teh above, if B1 is not greater than zero, return a blank string.
That blank string will evaluate to being greater than zero.
To correct the formula to work with the formula in N16, you could use
=IF(B1>0,A1/B1,0)

If you still can't get it to work, post the formula that you have in J13.

One more option.....
Add a third argument to the AND statement:
=IF(AND(ISNUMBER(J13),J13>0,B16="X"),1000,"N/A")

The above will be true only if all of the following are true.
What's in J13 is a number, that number is greater than 0 and B16 = "X"

John
Click to expand...
This way if J13 shows 0, N16 will show N/A. The formulas
you were given do exactly that.cases have to be true for the 1000 to appear. J13 "has"
to be greater than 0 while at the same time b16
equals "x".way to do this but after staring at it for so long I just
couldn't come up with the solution.stated in my problem. I think I should have said if J13
is >0 but not equal to 0. This way if J13 shows 0, N16
will show N/A.-----Original Message-----
Hi MPsr,-----Original Message-----
I know what I want the formula to do but cannot create
the end result. Here's what I hope you can solve.The results are to show in cell N16. If cell J13 is >0
then I want N16 to check cell B16 for "x" (text), if B16
is marked with an "x" then N16 is to show $1000, but if
B16 is blank then N16 is to show N/A.ELSE formula in this instance. Any help will be
appreciated!
 
MPSr,

Just to close the loop on this thread, your formula:
=if(iserror(j12/j11),"0",(j12/j11))
will return a zero as a text character (not a numerical zero).
To fix the original formula, you could use:
=if(iserror(j12/j11),0,(j12/j11))
without the quotes around the zero
and then you wouldn't need that extra argument in the
AND statement.

John
 
Back
Top