Forum Discussion
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="Sell", (H19*F19), G19="BTO", ((H19*F19)*-100)-(I19*F19), G19="BTC", ((H19*F19)*-100)-(I19*F19), G19="STC", ((H19*F19)*100)-(I19*F19), G19="STO", ((H19*F19)*100)-(I19*F19))
Any help would be greatly appreciated!
Edit: My version is Office Professional Plus 2016.
7 Replies
I only didn't catch why nested IF doesn't work. If use some formatting
=IFS( G19="Buy", H19*F19*-1, G19="Sell",H19*F19, G19="BTO", H19*F19*-100 -I19*F19, G19="BTC", H19*F19*-100 -I19*F19, G19="STC", H19*F19*100 -I19*F19, G19="STO", H19*F19*100 -I19*F19, TRUE, "wrong value" )it's easy to transform IFS back to nested IF
= IF(G19="Buy", H19*F19*-1, IF(G19="Sell",H19*F19, IF(G19="BTO", H19*F19*-100 -I19*F19, IF(G19="BTC", H19*F19*-100 -I19*F19, IF(G19="STC", H19*F19*100 -I19*F19, IF(G19="STO", H19*F19*100 -I19*F19, "wrong value" ))))))- Agreed
Now as we know the nature of the error perhaps a formula such as
= IF(LEFT(Type,1)="S", 1, -1) * volume*price - (MID(Type,2,1)="T") * volume*rate/100might do the job.
p.s. I have taken a blind guess at some names because I do not get on with direct cell referencing and have avoided them since 2015.
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.
Unfortunately, I get the same "#Name?" error with the switch formula as i do with the IFS formula I posted.
IFS() and SWITCH() are available since Excel 2019.
- 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.
