Multiple column scenarios

[Caly]

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”

 

[AlanY]

similar to Fluff's suggestion

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Price A			Price A OK?	Price B			Price B OK?	Price C			Price C OK?
2	5			Yes	6			Yes	7			Yes	5	5
3	5			No	6			Yes	7			Yes	6	6
4	5			Yes	6			No	7			Yes	5	5
5	5			No	6			No	7			Yes	7	7
6	5			No	6			Yes	7			No	6	6
7	5			Yes	6			Yes	7			Yes	5	5
Sheet1

Cell Formulas
Range	Formula
M2:M7	M2	=MIN(IF(D2="YES",A2,1000000),IF(H2="YES",E2,1000000),IF(L2="YES",I2,1000000))
N2:N7	N2	=MIN(IF(D2:L2="Yes",A2:I2))
Press CTRL+SHIFT+ENTER to enter array formulas.