mikes6058

asked on

# IF OR ANd STATEMENT - EXCEL - TWO CONDITIONS

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

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

However, if the YE extrapolated value for the row is >1,250,000 then apply the the first three if statements but not the forth. Effectively this means the return for any YE extrapolated values greater than 1,250,000 will be capped at "2"

The formula would return the following results for the rows above

row 1 - 2

row 2 - 0

row 3 - 2

row 4 - 3

row 5 - 0

row 6 - 0

Thanks

Mike

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

However, if the YE extrapolated value for the row is >1,250,000 then apply the the first three if statements but not the forth. Effectively this means the return for any YE extrapolated values greater than 1,250,000 will be capped at "2"

The formula would return the following results for the rows above

row 1 - 2

row 2 - 0

row 3 - 2

row 4 - 3

row 5 - 0

row 6 - 0

Thanks

Mike

ASKER

Sorry I should have specified. Please could you provide the solution using the same logic from the previous question, provided my ProfessorJimJam

=IF(AND(A1>0,A1<=10),1,IF(AND(A1>10,A1<=20),2,IF(AND(A1>20,A1<=30),3,IF(A1>30,4,0))))

=IF(AND(A1>0,A1<=10),1,IF(

ASKER

Reason being that it will transfer directly into DAX

You can use this user defied function. Usage: =YE_YE(A1:B1)

```
Function YE_YE(r As Range) As Integer
Select Case True
Case r(1) > 1250000
YE_YE = 2
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
```

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Sorry Martin I do not recognize that syntax

Please could you provide the solution using excel formula.

Mike

Please could you provide the solution using excel formula.

Mike

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks Rgonzo and Martin both brilliant solutions.

Thanks for explaining the VBA steps Martin

Thanks for explaining the VBA steps Martin

ASKER

Another very similar question to follow!

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you

Marty - Microsoft MVP 2009 to 2016

Experts Exchange MVE 2015

Experts Exchange Top Expert Visual Basic Classic 2012 to 2015

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you

*including one that explains how to write User Defined Functions like the one I wrote for this question.*Marty - Microsoft MVP 2009 to 2016

Experts Exchange MVE 2015

Experts Exchange Top Expert Visual Basic Classic 2012 to 2015

pls try

Open in new window

Regards