Take the MIN price checking against multiple (more than two) cells

[Caly]

Hi I am looking to see if there is a way to write a formula to take the minimum sell price between different cells but only where the margin is okay.

Currently I am writing it out in a long way to look at all cells and if they all say ok then take the MIN value; from there I am writing out all the different possibilities with multiple IF statements.

Is there a shorter way to do this?

Example
Col A Col B. Col C. col D
price 1. Margin. Price 2. Margin
$1 Ok. $2. Not ok

=if(and(B1=“ok”,D1=“Ok”),min(A1,C1),if(and(B1=“ok”,D1<>”Ok”),A1,if(and(B1<>”ok”,D1=“Ok”),C1,”No Go”

But is there a shorter way to write this? If there’s more than 2 criteria it gets very long. Any suggestions?

 

[Fluff]

Maybe

=MIN(IF(B1:D1="Ok",A1:C1))