Multiple column scenarios

Caly

New Member
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows

ADVERTISEMENT

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
    51B1800E-44BB-4D20-8EFA-FA183EB6CC10.jpeg
    252.4 KB · Views: 2

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,631
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+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
Joined
Jul 19, 2015
Messages
44
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
Thank you for your help
 

Attachments

  • 30EC7BEE-ED4E-48AB-93C1-2332DF02AD8F.jpeg
    30EC7BEE-ED4E-48AB-93C1-2332DF02AD8F.jpeg
    130.1 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,631
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top