?
Solved

IF OR ANd STATEMENT - EXCEL - TWO CONDITIONS

Posted on 2016-10-12
10
Medium Priority
?
53 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
[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
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41840503
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
ID: 41840508
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
ID: 41840513
Reason being that it will transfer directly into DAX
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 49

Expert Comment

by:Martin Liss
ID: 41840523
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 52

Accepted Solution

by:
Rgonzo1971 earned 1000 total points
ID: 41840528
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
 

Author Comment

by:mikes6058
ID: 41840530
Sorry Martin I do not recognize that syntax

Please could you provide the solution using excel formula.

Mike
0
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 1000 total points
ID: 41840561
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
ID: 41840570
Thanks Rgonzo and Martin both brilliant solutions.

Thanks for explaining the VBA steps Martin
0
 

Author Closing Comment

by:mikes6058
ID: 41840571
Another very similar question to follow!
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41840574
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 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