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(B 1>=5,A1<=1 0),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
Use similar logic displayed in formula below;
=MIN(IF(B1<=0),0,IF(AND(A1
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
pls try
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
ASKER
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
See table below. Also see attached as Excel file.
if-statement-test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try my formula up top?
ASKER
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
That worked brilliantly Rgonzo. Thanks a lot!
Mike
Open in new window