Caly
Board Regular
- Joined
- Jul 19, 2015
- Messages
- 159
- Office Version
- 365
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
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?
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?