SOLVED

Microsoft Excel Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-3120924%22%20slang%3D%22en-US%22%3EMicrosoft%20Excel%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3120924%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20adjust%20a%20spreadsheet%20so%20that%20if%20cell%20F2%20%3D%20the%20word%20%22put%22%20then%20this%20formula%20is%20used%26nbsp%3B%3DIF(J2%3D%22%22%2C%22%22%2C(I2-J2)*H2*100)%26nbsp%3B%20but%20if%20that%20same%20cell%20F2%20%3D%20the%20word%20%22call%22%20then%20this%20formula%20is%20to%20be%20used%26nbsp%3B%3DIF(J2%3D%22%22%2C%22%22%2C(J2-I2)*H3*100).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20to%20set%20up%20the%20formula%20so%20that%20you%20can%20have%20both%20formulas%20in%20that%20column%20whenever%20the%20word%20in%20column%20F%20%3D%20either%20call%20or%20put%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3120924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121007%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298060%22%20target%3D%22_blank%22%3E%40allyshirkie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20F2%20%3D%20%22put%22%2C%20%0A%20%20%20%20%20IF(%20J2%3D%22%22%2C%20%22%22%2C(I2-J2)*H2*100)%2C%0A%20IF(%20F2%20%3D%20%22call%22%2C%0A%20%20%20%20%20IF(%20J2%3D%22%22%2C%20%22%22%2C(J2-I2)*H3*100)%2C%0A%20%20%20%20%20%22no%20put%20no%20call%22%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EMost%20probably%20could%20be%20simplified%2C%20depends%20on%20logic%20which%20was%20not%20articulated%20directly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121084%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Excel%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121084%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298060%22%20target%3D%22_blank%22%3E%40allyshirkie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20possibility%20is%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20IF(J2%26lt%3B%26gt%3B%22%22%2C%20SWITCH(F2%2C%22put%22%2CH2%2C%22call%22%2C-H3)%20*%20(I2-J2)*100%2C%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

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

@allyshirkie 

One possibility is 

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

 

@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

Thank you for the help, that formula worked perfectly!

@allyshirkie , you are welcome