Formula doesn't compute. Can you figure it out?

[Jetheat]

I have this formula at the end of a row in column J.

=IF(AND(E12<>"B",E12<>"S"),"",IF(E12="B",G12-F12,F12-G12)/IF(C12="JPY",100,1)

It says that if Cell E12 is anything other than "B" or "S" than leav
this cell Blank, otherwise do this formul
IF(E12="B",G12-F12,F12-G12)/IF(C12="JPY",100,1)

The cell is not blank and it contains #VALUE instead.

How do I get rid of #VALUE and leave the cell blank unless data i
entered into the cells defined in the formula

 

[Bob Phillips]

You must have a text value in G12 or F12

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Niek Otten]

Probably one of the cells (E12,B12,C12 or F12) is #VALUE.

Anyway,

=IF(AND(E12<>"B",E12<>"S"),""

will never return "".
Suppose E12 contains "B" then E12<>"S" is FALSE; if it contains "S" then
E12<>"B" is FALSE. You probably mean OR instead of AND.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Jetheat]

Ok, I figured out what it is but don't know how to correct it.

The full formula is

=IF(AND(E18<>"B",E18<>"S"),"",IF(E18="B",G18-F18,F18-G18))/IF(D18="JPY",100,1)

If the row is empty, the middle part of the formul
[IF(E18="B",G18-F18,F18-G18)] = 0 and then the formula tries to us
that 0 for division, so its trying to do 0/100 or 0/1 which is probabl
why its returning the error.

How do I chage the formula to correct this?

Thank

 

[Jetheat]

The AND portion equates to TRUE,

IF(E18="B",G18-F18,F18-G18) equates to 0

this is what it look like at one point:

=IF(TRUE,"",0)/IF(D18="JPY",1)

Looks wrong

 

[Bob Phillips]

0/100 and 0/1 is 0, not #VALUE.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Ok, I figured out what it is but don't know how to correct it.

The full formula is

If the row is empty, the middle part of the formula
[IF(E18="B",G18-F18,F18-G18)] = 0 and then the formula tries to use
that 0 for division, so its trying to do 0/100 or 0/1 which is probably
why its returning the error.

How do I chage the formula to correct this?

Thanks

 

[Bob Phillips]

That should not be the problem as formulas equate to True or False, and then
take the appropriate path.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JWolf]

=IF(OR(ISTEXT(F12),ISTEXT(G12)),"",IF(E12="B",G12-F12,F12-G12)/IF(C12="JPY",100,1))