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

--
Message posted from http://www.ExcelForum.com

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)

"Jetheat " wrote in message
...
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 leave
this cell Blank, otherwise do this formula
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 is
entered into the cells defined in the formula?


---
Message posted from http://www.ExcelForum.com/

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 " wrote in message
...
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 leave
this cell Blank, otherwise do this formula
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 is
entered into the cells defined in the formula?


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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)

"Jetheat " wrote in message
...
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 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


---
Message posted from http://www.ExcelForum.com/

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)

"Jetheat " wrote in message
...
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!


---
Message posted from http://www.ExcelForum.com/

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


Jetheat < wrote:

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 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


---
Message posted from http://www.ExcelForum.com/