Writing complicated If statement in Excel

J

jfiouzi

I am trying to write the following in excel, I know how to do it in VB
but was told that you can also write it in excel and I would like t
know how, would appreciate all your help. It is an option pricin
model and all I want to say is:

For K and F, if K=F then return "atm"

If K<F then return as follows: (K-F)/F and "itm" (% that option is i
the money)

If K>F then return as follows: (F-K)/F and "otm" (% that option is ou
of the money)

Thank you.

Jenu
 
F

Frank Kabel

Hi
try
=IF(K1=F1,"atm",IF(K1<F1),TEXT((K1-F1)/F1,"0.00") & "
itm",TEXT((F1-K1)/F1,"0.00") & " otm"))
 
J

Jan Karel Pieterse

Hi Jfiouzi,
For K and F, if K=F then return "atm"

If K<F then return as follows: (K-F)/F and "itm" (% that option is in
the money)

If K>F then return as follows: (F-K)/F and "otm" (% that option is out
of the money)
Click to expand...

Let us say K is in cell A1 and F is in cell B1:

=IF(A1=B1,"atm",IF(A1<B1,(A1-B1)/B1 & " itm",(B1-A1)/B1 & " otm"))

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
B

BrianB

I think this is it :-

=IF(K1=F1,"atm",ABS((K1-F1)/F1*100)&IF(K1>F1," % itm"," % otm")
 
J

jammy

=IF(K=F,"atm",IF(K>F,(F-K)/F&" otm",(K-F)/F&" itm"))

will return "atm" if they are equal, <result> otm if K>F and <result
itm if K<F.

e.g. just random numbers

312 9 -33.6666666666667 otm
5 5 atm
25 45 -0.444444444444444 it
 
D

Dana DeLouis

Would the following idea work?

=(K1-F1)/F1

with a custom format or Style of

-0.0% "itm";0.0% "otm";"atm"
 
Top