 SOLVED

# Microsoft Excel Formula help

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?

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Microsoft Excel Formula help

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.

# Re: Microsoft Excel Formula help

One possibility is

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

# Re: Microsoft Excel Formula help

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

# Re: Microsoft Excel Formula help

Thank you for the help, that formula worked perfectly!

# Re: Microsoft Excel Formula help

@allyshirkie , you are welcome