Microsoft Excel Formula help

[allyshirkie]

I am trying to adjust a spreadsheet so that if cell F2 = the word "put" then this formula is used =IF(J2="","",(I2-J2)*H2*100)  but if that same cell F2 = the word "call" then this formula is to be used =IF(J2="","",(J2-I2)*H3*100). 

is there a way to set up the formula so that you can have both formulas in that column whenever the word in column F = either call or put? 

[#2 · Accepted solution]

@allyshirkie 

As variant

=IF( F2 = "put", 
     IF( J2="", "",(I2-J2)*H2*100),
 IF( F2 = "call",
     IF( J2="", "",(J2-I2)*H3*100),
     "no put no call" ) )

Most probably could be simplified, depends on logic which was not articulated directly.

[#3]

@allyshirkie 

One possibility is 

= IF(J2<>"", SWITCH(F2,"put",H2,"call",-H3) * (I2-J2)*100, "")

[#4]

@Peter Bartholomew 

Most probably you are right, but to follow request literally

- we first check on put and call

if one of the above

then

      if J2 is empty

      then ""

      else SWITCH

else

     inform that logic is not defined

[#5]

Thank you for the help, that formula worked perfectly!

[#6]

@allyshirkie , you are welcome