Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
3 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
IF OR ANd STATEMENT - EXCEL - TWO CONDITIONS
Posted on 2016-10-12
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
[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
-
5
3-
2
10 Comments
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(
=IF(AND(A1>0,A1<=10),1,IF(
Active today
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
Active today
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))
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
Active today
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.
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.
Active today
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
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
Featured Post
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!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month3 days, 9 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.