Solved

IF OR ANd STATEMENT - EXCEL - TWO CONDITIONS

Posted on 2016-10-12
10
31 Views
Last Modified: 2016-10-12
I need help writing an if statement that is dependent on the conditions from two cell refs - see below.


If two cons
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
0
Comment
Question by:mikes6058
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

pls try

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

Open in new window

Regards
0
 

Author Comment

by:mikes6058
Comment Utility
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))))
0
 

Author Comment

by:mikes6058
Comment Utility
Reason being that it will transfer directly into DAX
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
Comment Utility
then try
=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))

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mikes6058
Comment Utility
Sorry Martin I do not recognize that syntax

Please could you provide the solution using excel formula.

Mike
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
Comment Utility
You can use my code exactly like a formula. To do so

In Excel, Press Alt+F11 to open the Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window). If you don’t see an existing module then select Insert -> Module from the context menu. Otherwise just select the module.

Copy the code (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Then in C1 (or wherever)  just type in =YE_YE(A1:B1) and copy down.
0
 

Author Comment

by:mikes6058
Comment Utility
Thanks Rgonzo and Martin both brilliant solutions.

Thanks for explaining the VBA steps Martin
0
 

Author Closing Comment

by:mikes6058
Comment Utility
Another very similar question to follow!
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now