# Multiple column scenarios

#### Caly

##### New Member
Hi I have multiple columns each with a section of data to feed into the criteria. 3 various prices that based on their 3 associated columns will have either yes or no. This results in multiple scenarios. Example is below of the formula but is there an easier way to write this formula?

=If(and(Price A=Yes,price b=yes,price c=yes), Min(price a, price b, price c),
If(and(Price a=no, price b=yes, price c=yes), min(price b, price c),
If(and(Price a=yes,price b=no,price c=yes), min(price a, price c),
If(and(price a=yes, price b=yes, price c=no),
Min(price a, price b),
If(and(Price a=yes,price b=no,price c=no), price a,
If(and(Price a=no,price b=yes,price c=no), price b,
If(and(Price a=no,price b=no,price c=yes), price c,
“Check”

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### AlanY

##### Well-known Member
try this

Book1
ABC
1A100Y
2B200N
3C300Y
4
5min100
Sheet1
Cell Formulas
RangeFormula
B5B5=MINIFS(B1:B3,C1:C3,"Y")
Cells with Data Validation
CellAllowCriteria
C1:C3ListY, N

#### AlanY

##### Well-known Member
with Excel 2013, try this

Book1
ABC
1A100Y
2B200N
3C300Y
4
5min100
Sheet1
Cell Formulas
RangeFormula
B5B5=MIN(IF(C1:C3="Y",B1:B3))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
C1:C3ListY, N

#### Caly

##### New Member
Thank you do much. But the options are across and not up and down. So price A, B and C with their yes or no results are across the rows in different columns and not on different rows. Is there a way to write the formula for this?

#### Caly

##### New Member

Thank you do much. But the options are across and not up and down. So price A, B and C with their yes or no results are across the rows in different columns and not on different rows. Is there a way to write the formula for this?

#### Attachments

• 51B1800E-44BB-4D20-8EFA-FA183EB6CC10.jpeg
252.4 KB · Views: 2

#### AlanY

##### Well-known Member
not the most elegant way, but check if that works for you

Book1
ABCDEFG
1Price APrice A OK?Price BPrice B OK?Price CPrice C OK?
25Yes6Yes7Yes5
35No6Yes7Yes6
45Yes6No7Yes5
55No6No7Yes7
65No6Yes7No6
75Yes6Yes7Yes5
Sheet1
Cell Formulas
RangeFormula
G2:G7G2=MIN(IF(B2="YES",A2,1000000),IF(D2="YES",C2,1000000),IF(F2="YES",E2,1000000))
Cells with Data Validation
CellAllowCriteria
D2:D7ListYes, No
F2:F7ListYes, No
B2:B7ListYes, No

#### Fluff

##### MrExcel MVP, Moderator

+Fluff v2.xlsm
ABCDEFGH
1
25Yes6Yes7Yes5
36no7no8Yes8
47No8Yes9Yes8
58no9Yes10No9
69No10no11yes11
Summary
Cell Formulas
RangeFormula
H2:H6H2=MIN(IF(C2:G2="Yes",B2:F2))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Caly

##### New Member
Thank you. I getting a #value! Error code unfortunately

There will be additional data set columns between the price to select the minimum of and if the price says yes or no. The formula it works and yields the correct answer but is just very long. So was trying to write something simpler.
The min of suggestions are great but I am getting an error code

attached is an image

#### Attachments

130.1 KB · Views: 2

#### Fluff

##### MrExcel MVP, Moderator
Thank you. I getting a #value! Error code unfortunately
Who are you talking to here?

Also can you please post some sample data to the board using the XL2BB add-in, we cannot manipulate an image.

#### AlanY

##### Well-known Member
you need to adjust the formula with the extra columns like this

Book1
ABCDEFGHIJKLM
1Price APrice A OK?Price BPrice B OK?Price CPrice C OK?
25Yes6Yes7Yes5
35No6Yes7Yes6
45Yes6No7Yes5
55No6No7Yes7
65No6Yes7No6
75Yes6Yes7Yes5
Sheet1
Cell Formulas
RangeFormula
M2:M7M2=MIN(IF(D2="YES",A2,1000000),IF(H2="YES",E2,1000000),IF(L2="YES",I2,1000000))

Replies
6
Views
77
Replies
2
Views
32
Replies
7
Views
270
Replies
3
Views
41
Replies
5
Views
73

1,129,727
Messages
5,638,019
Members
416,999
Latest member
smulttjukken

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back