# IF OR formula Excel - multiple conditions

I need help writing an if statement that is dependent on the conditions from two cell refs - see below.

Use similar logic displayed in formula below;

=MIN(IF(B1<=0),0,IF(AND(A1>=0,B1<5),1,IF(AND(B1>=5,A1<=10),2,IF(A1>=10,3,0)))) ,IF(A1>1250000,2,3))

IF YE Extrapolated <15,000 then return 0

If YE extrapolated >=15,000<50,000 then apply the following IF statements;

IF Pre YE <=0 then return 0

OR

IF Pre YE >0<5 then return 1

OR

IF Pre YE >=5<10 then return 2

OR

IF Pre YE >=10 then return 3

IF YE extrapolated is >=50,000 <1,250,000 then apply the following IF statements;

IF Pre YE <=0 then return 0

OR

IF Pre YE >0<5 then return 1

OR

IF Pre YE >=5<10 then return 2

OR

IF Pre YE >=10 then return 4

IF YE extrapolated is >=1,250,000 then apply the following IF statements;
IF Pre YE <=0 then return 0

OR

IF Pre YE >0<5 then return 1

OR

IF Pre YE >=5 then return 3

See expected result highlighted in yellow

Mike
###### Who is Participating?

Commented:
then try
``````=IF(A1<15000,0,IF(AND(A1>=15000,A1<50000),IF(B1<=0,0,IF(AND(B1>0,B1<5),1,IF(AND(B1>=5,B1<=10),2,IF(A1>=10,3,0)))),IF(AND(A1>=50000,A1<1250000),IF(B1<=0,0,IF(AND(B1>0,B1<5),1,IF(AND(B1>=5,B1<=10),2,IF(A1>=10,4,0)))),IF(B1<=0,0,IF(AND(B1>0,B1<5),1,IF(B1>=5,3))))))
``````
0

Something like this?
``````=IF(OR(A2<15000, C2<=0), 0, IF(C2< 0.05, 1, IF(AND(A2<1250000, C2<0.1), 2, IF(AND(A2>=50000,A2<125000), 4, 3))))
``````

Where A2 is the first row of data in the YE Extrapolated column, and C2 is the first row of data in the Pre YE column.
0

Older than dirtCommented:
I don't know if you want to use a UDF like me last one but...
``````Function YE_YE(r As Range) As Integer

Select Case True
Case r(1) < 15000
YE_YE = 0
Case r(1) >= 15000 & r(1) < 50000
Select Case True
Case r(2) <= 0
YE_YE = 0
Case r(2) > 0 And r(2) < 5
YE_YE = 1
Case r(2) >= 5 And r(2) < 10
YE_YE = 2
Case r(2) >= 10
YE_YE = 3
End Select
Case r(1) >= 50000 & r(1) < 125000
Select Case True
Case r(2) <= 0
YE_YE = 0
Case r(2) > 0 And r(2) < 5
YE_YE = 1
Case r(2) >= 5 And r(2) < 10
YE_YE = 2
Case r(2) >= 10
YE_YE = 4
End Select
Case r(1) >= 1250000
Select Case True
Case r(2) <= 0
YE_YE = 0
Case r(2) > 0 And r(2) < 5
YE_YE = 1
Case r(2) >= 5
YE_YE = 3
End Select
End Select

End Function
``````
0

Author Commented:
Hi Martin,

I tried using the UDF but the results were not the same as the expected results.

0
3
3
3
3
3

Could you show me the native excel formula used?
0

Older than dirtCommented:
Sorry, I didn't notice that you had changed the data as compared to your other question. I can't help you with a native formula but this will give you the results you show. Note that the usage is now =YE_YE(A1,C1)   Note the comma instead of the colon.

``````Function YE_YE(rA As Range, rC) As Integer

Select Case True
Case rA < 15000
YE_YE = 0
Case rA >= 15000 And rA < 50000
Select Case True
Case rC <= 0
YE_YE = 0
Case rC > 0 And rC < 5
YE_YE = 1
Case rC >= 5 And rC < 10
YE_YE = 2
Case rC >= 10
YE_YE = 3
End Select
Case rA >= 50000 And rA < 125000
Select Case True
Case rC <= 0
YE_YE = 0
Case rC > 0 And rC < 5
YE_YE = 1
Case rC >= 5 And rC < 10
YE_YE = 2
Case rC >= 10
YE_YE = 4
End Select
Case rA >= 1250000
Select Case True
Case rC <= 0
YE_YE = 0
Case rC > 0 And rC < 5
YE_YE = 1
Case rC >= 5
YE_YE = 3
End Select
End Select

End Function
``````
0

Commented:
Hi,

pls try
``````=IF(A1<15000,0,MIN(IF(B1<=0,0,IF(AND(A1>0,B1<5),1,IF(AND(B1>=5,A1<=10),2,IF(A1>=10,3,0)))),IF(A1>1250000,2,3)))
``````
Regards
0

Author Commented:
The results I am getting are different from what I would expect.

See table below. Also see attached as Excel file.

if-statement-test.xlsx
0

Did you try my formula up top?
0

Author Commented:
I did Shaun, but I was getting a syntax error and couldn't figure out what it was. Thanks for you contribution though.

That worked brilliantly Rgonzo. Thanks a lot!

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.