Processing
 

Excel More conditions in one formula?

19/09/2016 09:34#1

Dean Zuna

Member

Joined at: 5 months ago

Post: 1

Thank: 0

Thanked: 0

Can I have those conditions here packed in one formula?.

1. If the price (D2) is <= 20 and ROI (J2) is <= 10 then true or false. 2. If the price (D2) is between 20 and 60 and ROI (J2) is <= 5 then true or false. 3. If the price (D2) is between 60 and 150 and ROI (J2) is <= 4 then true or false. 4. If the price (D2) is between 150 and 500 and ROI (J2) is <= 3 then true or false. 5. If the price (D2) is >= 500 and ROI (J2) is <= 2.5 then true or false. 
19/09/2016 09:55Top#2

Boris

Member

Joined at: 11 months ago

Post: 37

Thank: 0

Thanked: 0

Based on what you asked here is the formula:

=IF(AND(D2<=20,ROI(J2)<=10),"Output1",IF(AND(D2<60,ROI(J2)<=5),"Output2",IF(AND(D2<150,ROI(J2)<=4),"Output3",IF(AND(D2<500,ROI(J2)<=3),"Output4",IF(AND(D2>=500,ROI(J2)<=2.5),"Output5",FALSE))))) 
19/09/2016 21:08Top#3

Joined at: 10 months ago

Post: 2

Thank: 0

Thanked: 0

The following is a bit shorter and easier to read:

=IFERROR("Output"&MATCH(1,(D2>={0,20,60,150,500})(D2<={20,60,150,500,99999})(J2<={10,5,4,3,2.5}),0),FALSE)

Similar articles

Excel More conditions in one formula?

5 months ago - Reply: 2 - Views: 172

Get Workbook reference from Open File dialog

5 months ago - Reply: 2 - Views: 130

Add Calculated Field To Pivot Table

6 months ago - Reply: 0 - Views: 7

Nested IF Functions in Excel not working

6 months ago - Reply: 3 - Views: 164

Formulating Date in Excel 2013

6 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

6 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

6 months ago - Reply: 1 - Views: 23

excel search and copy in/from another file

6 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

6 months ago - Reply: 0 - Views: 6

Excel static conditional formatting area

6 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re