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

Rgonzo1971

Hi,

pls try

=MIN(MATCH(B1,{-10000;0;5;10},1)-1,IF(A1>1250000,2,4))

Open in new window

Regards

mikes6058

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

mikes6058

ASKER

Reason being that it will transfer directly into DAX

Martin Liss

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

Open in new window

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mikes6058

ASKER

Sorry Martin I do not recognize that syntax

Please could you provide the solution using excel formula.

Mike

SOLUTION

Martin Liss

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mikes6058

ASKER

Thanks Rgonzo and Martin both brilliant solutions.

Thanks for explaining the VBA steps Martin

mikes6058

ASKER

Another very similar question to follow!

Martin Liss

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