Multiple column scenarios

Caly

Board Regular
Joined
Jul 19, 2015
Messages
158
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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: 3
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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: 3
Upvote 0
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. ;)
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top