Using multiple IF functions

W

weedevil

I'm at my wits end...

Here is a formula I have used to determine a cell calculation dependan
on input in another cell(d4).

=IF(D4="Short",((I4-F4)/F4)*-1,IF(D4="Long",(I4-F4)/F4))

The problem is that if D4 is empty it still returns a result. I do no
want this to happen. The cell should remain blank until D4 is filled
Any suggestions to how I allow for this?

Many thanks.

And
 
A

Alan

You could try,
=IF(D4=0,0,IF(D4="Short",((I4-F4)/F4)*-1,IF(D4="Long",(I4-F4)/F4)))
or
=IF(D4=0,"",IF(D4="Short",((I4-F4)/F4)*-1,IF(D4="Long",(I4-F4)/F4)))
Regards
 
A

Alan

Or this will only give a result if D4 is 'Short' or 'Long' ie a typo like
'Sgort' will return zero or null
=IF(AND(D4<>"Short",D4<>"Long"),0,IF(D4="Short",((I4-F4)/F4)*-1,IF(D4="Long",(I4-F4)/F4)))
=IF(AND(D4<>"Short",D4<>"Long"),"",IF(D4="Short",((I4-F4)/F4)*-1,IF(D4="Long",(I4-F4)/F4)))
Regards,
 
Top