IF AND OR Not correct

honkin

Board Regular
Joined
Mar 20, 2012
Messages
208
Office Version
  1. 2016
Platform
  1. MacOS
I have a formula to try and make a calculation of profit or loss, but cannot seem to get it to function correctly

C is the number of runners
R is the price
T is the finish position

Here is the formula
Code:
=IF(OR(AND(C2<=3,R2<=20,T2>1),-80, AND(C2>=4,C2<=7,R2<=20,T2>2),-80, AND(C2>=8,R2<=20,T2>3),-80),78.4*S2-78.4,0)

So what I am trying to achieve is the following:

IF C2<=3 & R2<=20 & T2=1 then perform the profit calculation. Otherwise the answer-80 (if R2 is >20 the answer should be 0)
or
IF C2>=4 & C2<=7 & R2<=20 & T2<=2 then perform the profit calculation. Otherwise the answer is -80 (if R2 is >20 the answer should be 0)
or
IF C2>=8 & R2<=20 & T2<=3 then perform the profit calculation. Otherwise the answer -80 (if R2 is >20 the answer should be 0)

So the R2<=20 seems to be the vital part, as if R2 >20, then the result will always be zero - no calculations necessary at all. If R2 is <=20, then the other criteria need to be used, with either a profit calculation or loss of -80 showing. Only 3 possibilities, 0, Profit or -80

There are obviously some flaws in this, but it is beyond me to figure it out.

Thanks in advance
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

honkin

Board Regular
Joined
Mar 20, 2012
Messages
208
Office Version
  1. 2016
Platform
  1. MacOS
Just as a follow up, here is the same code as above without the price check in R2. Well, it is slightly different, as I moved the -80 around to try and accommodate the price check, but couldn't get it to work.This code works flawlessly, but just does not have the price check

Code:
=IF(OR(AND(C2<=3,T2=1), AND(C2>=4,C2<=7,T2<=2), AND(C2>=8,T2<=3)),78.4*S2-78.4,-80)

I just need this whole formula to also check that if R2 >=20, then the answer is always 0

Any help gladly accepted
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Like this?

=IF(R2>=20,0,IF(OR(AND(C2<=3,T2=1), AND(C2>=4,C2<=7,T2<=2), AND(C2>=8,T2<=3)),78.4*S2-78.4,-80))
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
208
Office Version
  1. 2016
Platform
  1. MacOS
Like this?

=IF(R2>=20,0,IF(OR(AND(C2<=3,T2=1), AND(C2>=4,C2<=7,T2<=2), AND(C2>=8,T2<=3)),78.4*S2-78.4,-80))
Ah you're a legend mate. Yep, that seems to handle it perfectly. Now I need to learn from that in case I ever have something similar. I can see the structure of this, so it helps

cheers
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad it helped. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,524
Messages
5,625,309
Members
416,092
Latest member
dodovisk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top