Solved

IF OR formula Excel

Posted on 2016-10-12
8
60 Views
Last Modified: 2016-10-12
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
0
Comment
Question by:mikes6058
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 250 total points
ID: 41840272
=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
 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 total points
ID: 41840274
Hi,

pls try

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

Open in new window

Regards
0
 

Author Comment

by:mikes6058
ID: 41840312
Great, how would this work in DAX formula

assuming A1 is a column reference?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41840361
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 41840373
Mike,

i did not see anything about DAX in your original post.

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

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points
ID: 41840387
Looking at that link from ProfJJ, it would seem that my suggestion would become:

=MAX(1,MIN(4,CEILING([ColumnHeader],10)/10))   where ColumnHeader refers to header of column to be considered.

Thanks
Rob H
0
 

Author Comment

by:mikes6058
ID: 41840421
Great, all good solutions.

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

Mike
0
 

Author Comment

by:mikes6058
ID: 41840423
nice work
0

Featured Post

Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question