Link to home
Start Free TrialLog in
Avatar of mikes6058
mikes6058

asked on

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))

User generated image


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
SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

Avatar of mikes6058
mikes6058

ASKER

Hi Martin,

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

Instead I got,

0
3
3
3
3
3

Could you show me the native excel formula used?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)))

Open in new window

Regards
The results I am getting are different from what I would expect.

See table below. Also see attached as Excel file.

User generated imageif-statement-test.xlsx
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you try my formula up top?
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