Forum Discussion

BradH5's avatar
BradH5
Copper Contributor
Mar 30, 2021

Need help with multiple IFs formula

I can't do this with a traditional IF formula. So, I've been trying to work out the IFS formula. It seems this should work but, something is off somewhere:   =IFS(G19="Buy", ((H19*F19)*-1), G19="Se...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 30, 2021

BradH5 

The formula appears valid.  You could also use

= SWITCH(G19,
  "Buy", ((H19*F19)*-1),
  "Sell", (H19*F19),
  "BTO", ((H19*F19)*-100)-(I19*F19),
  "BTC", ((H19*F19)*-100)-(I19*F19),
  "STC", ((H19*F19)*100) -(I19*F19),
  "STO", ((H19*F19)*100) -(I19*F19))

I wouldn't know whether 'something is a bit off' since I have no idea what you expect or what F19, H19 and I19 might contain.  G19 seems to be codes that have something to do with trading but why "STO" appears to be out of order or why it does not deserve a minus sign, I cannot even guess.

  • BradH5's avatar
    BradH5
    Copper Contributor
    Mar 30, 2021

    Unfortunately, I get the same "#Name?" error with the switch formula as i do with the IFS formula I posted.

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Mar 31, 2021

      BradH5 

      IFS() and SWITCH() are available since Excel 2019.

  • BradH5's avatar
    BradH5
    Copper Contributor
    Mar 30, 2021
    Thank you for the prompt reply. This is for a trade tracking sheet. STO means sell to open for complex options trades. I haven't used the switch formula before. Pretty odd since I've been using excel as long as it has been around! I'll try it and report back.

Resources