IF/AND/OR or Something else.....

[James8761]

I am trying to write a complexed formula (for me).



What I want to say is…..IF M3, N3 or O3, = “Home” and AE3=”Home” then times V3, W3, or X3 by P3, Q3 or R3 (if Home is in M3, then it is V3*P3*.98, If Home is in N3 then it is W3*Q3*.98, if Home is in O then it is X3*R3*.98)

IF M3, N3 or O3, = “Home” and AE3=”Draw” then I want to say -V3 (If Home is in M3), -W3 (If Home is in N3) or -X3 (If Home is in O3)

IF M3, N3 or O3, = “Home” and AE3=”Away” then I want to say -V3 (If Home is in M3), -W3 (If Home is in N3) or -X3 (If Home is in O3)






The formula which I have completely messed up now is currently…. =IF(AND(M3="home",AE3="home"),V3*P3*0.98,""),IF(AND(M3="home",AE3="draw"),V3*P3*0.98,-V3),IF(AND(M3="home",AE3="away"),V3*P3*0.98,-V3)

 

[jasonb75]

Your conditions for Draw an Away don't make sense, I've interpreted it in 2 ways without analysing it too much,

=IF(AE3="Home",SUMPRODUCT(--(M3:O3="Home"),P3:R3,V3:U3)*0.98,-SUM(V3:X3))

If this is not what you want, then a clearer example will be required to explain it better. Please use the XL2BB add in to post your example instead of screen captures or copy and paste.
In that example you should provide several different combinations of home, away, etc with data in the various columns. Then enter a simple formula for each row showing the correct result, for example =W3*Q3*0.98 if that would be the correct result for that row. Do not use any IF's or other functions in the example.

 

[Fluff]

How about
=IF(AE3="Home",0.98*CHOOSE(MATCH("Home",M3:O3,0),V3*P3,W3*Q3,X3*R3),IF(OR(AE3={"Away","Draw"}),-CHOOSE(MATCH("Home",M3:O3,0),V3,W3,X3),""))

 

[James8761]

Your conditions for Draw an Away don't make sense, I've interpreted it in 2 ways without analysing it too much,

=IF(AE3="Home",SUMPRODUCT(--(M3:O3="Home"),P3:R3,V3:U3)*0.98,-SUM(V3:X3))

If this is not what you want, then a clearer example will be required to explain it better. Please use the XL2BB add in to post your example instead of screen captures or copy and paste.
In that example you should provide several different combinations of home, away, etc with data in the various columns. Then enter a simple formula for each row showing the correct result, for example =W3*Q3*0.98 if that would be the correct result for that row. Do not use any IF's or other functions in the example.

Thanks for the reply Jason. I have downloaded the Addin but for some reason it doesn't work for me.

 

[James8761]

How about
=IF(AE3="Home",0.98*CHOOSE(MATCH("Home",M3:O3,0),V3*P3,W3*Q3,X3*R3),IF(OR(AE3={"Away","Draw"}),-CHOOSE(MATCH("Home",M3:O3,0),V3,W3,X3),""))

That's excellent, thanks Fluff. Will save me a great deal of time...

 

[Fluff]

Glad we could help & thanks for the feedback.
Using Jason's idea, try
=IF(AE3="","",IF(AE3="Home",SUMPRODUCT(--(M3:O3="Home"),P3:R3,V3:X3)*0.98,SUMPRODUCT(--(M3:O3="Home"),-V3:X3)))