• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

IF OR ANd STATEMENT - EXCEL - TWO CONDITIONS

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
mikes6058
Asked:
mikes6058
  • 5
  • 3
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

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

Open in new window

Regards
0
 
mikes6058Author Commented:
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
 
mikes6058Author Commented:
Reason being that it will transfer directly into DAX
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Martin LissOlder than dirtCommented:
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
 
Rgonzo1971Commented:
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
 
mikes6058Author Commented:
Sorry Martin I do not recognize that syntax

Please could you provide the solution using excel formula.

Mike
0
 
Martin LissOlder than dirtCommented:
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
 
mikes6058Author Commented:
Thanks Rgonzo and Martin both brilliant solutions.

Thanks for explaining the VBA steps Martin
0
 
mikes6058Author Commented:
Another very similar question to follow!
0
 
Martin LissOlder than dirtCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now