# IF OR formula Excel

can any one help me with one formula which will solve the following if statement?

IF A1 is >0<=10 then return 1

OR

IF A1 is >10<=20 then return 2

OR

IF A1 is >20<=30 then return 3

OR

IF A1 is >30 then return 4
###### Who is Participating?

Commented:
=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))))
0

Commented:
Hi,

pls try

``````=5-MATCH(A1,{10000;30;20;10;0},-1)
``````
Regards
0

Author Commented:
Great, how would this work in DAX formula

assuming A1 is a column reference?
0

Finance AnalystCommented:
Alternative but don't know about converting to DAX.

=MAX(1,MIN(4,CEILING(A1,10)/10))

CEILING(Number,Factor)  rounds up a number to the next unit of factor, 10 in this instance.

Dividing by 10 then gives a single integer. Using MIN to compare with 4 ensures it doesn't go higher than 4; using MAX to compare with 1 ensures it doesn't go below 1, otherwise a zero value would give zero result.
0

Commented:
Mike,

you can use the same way as i described with DAX as well, except that the cell reference will not be A1 and it will be the column structured reference inside brakets

https://msdn.microsoft.com/en-us/library/ee634824.aspx
0

Finance AnalystCommented:
Looking at that link from ProfJJ, it would seem that my suggestion would become:

Thanks
Rob H
0

Author Commented:
Great, all good solutions.

Hadn't seen the Rob or Rgonzo's techniques before :)

Mike
0

Author Commented:
nice work
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.