Multiple criteria to choose Min or Max

[Caly]

Hi I have 5 columns of data each with values determining if the margin is ok. I currently have a very long formula to see if the MIN value is needed between each of the 5 groupings which is ok but then if the toggle says to take the MAX instead then the formula repeats except to take the MAX of all sections whichever is ok.



Is there a better and cleaner shorter way to find this?



Example

Column A - option 1 price

Column b - option margin

Column c - option 2 price

Column d - option 2 margin

Column e - option 3 price

Column f - option 3 margin

Column g - option 4 price

Column h - option 4 margin

Column i - option 5 price

Column j - option 5 margin



Toggle to do either min or max is in column A row 1



Each option could have a value or no value in the price and the margin will either say ok or not okay or no data

In the formula I have it looking if the margin says ok or does not say ok and taking the money or max depending on the toggle and none of the scenarios are true then it will say do not include.



Current formula:

=if(and($A$1=“Min”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin=“ok”,opt 4 margin=“ok”,opt 5 margin=“ok”),min(opt1 price,opt2,opt3,opt4,opt5), if(and($A$1=“Min”,opt1<>”ok”,opt2=“ok”,opt3=“ok”,opt4=“ok”,opt5=“ok”),min(opt2,3,4,5), if(and($A$1=“Min”,option 1 margin=“ok”,opt 2 margin<>”ok”,opt 3 margin=“ok”,opt 4 margin=“ok”,opt 5 margin=“ok”),min(opt1 price,opt3,opt4,opt5), if(and($A$1=“Min”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin<>”ok”,opt 4 margin=“ok”,opt 5 margin=“ok”),min(opt1 price,opt2,opt4,opt5), if(and($A$1=“Min”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin=“ok”,opt 4 margin<>”ok”,opt 5 margin=“ok”),min(opt1 price,opt2,opt3,opt5), if(and($A$1=“Min”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin=“ok”,opt 4 margin=“ok”,opt 5 margin<>”ok”),min(opt1 price,opt2,opt3,opt4), if(and($A$1=“Min”,option 1 margin=“ok”),opt1, if(and($A$1=“Min”,opt 2 margin=“ok”),opt2, if(and($A$1=“Min”,opt 3 margin=“ok”),opt3, if(and($A$1=“Min”,option 4margin=“ok”),opt4, if(and($A$1=“Min”,option 5margin=“ok”),opt5,
if(and($A$1=“Max”option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin=“ok”,opt 4 margin=“ok”,opt 5 margin=“ok”),max(opt1 price,opt2,opt3,opt4,opt5), if(and($A$1=“Max”,opt1<>”ok”,opt2=“ok”,opt3=“ok”,opt4=“ok”,opt5=“ok”),max(opt2,3,4,5), if(and($A$1=“Max”,option 1 margin=“ok”,opt 2 margin<>”ok”,opt 3 margin=“ok”,opt 4 margin=“ok”,opt 5 margin=“ok”),max(opt1 price,opt3,opt4,opt5), if(and($A$1=“Max”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin<>”ok”,opt 4 margin=“ok”,opt 5 margin=“ok”),max(opt1 price,opt2,opt4,opt5), if(and($A$1=“Max”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin=“ok”,opt 4 margin<>”ok”,opt 5 margin=“ok”),max(opt1 price,opt2,opt3,opt5), if(and($A$1=“Max”,option 1 margin=“ok”,opt 2 margin=“ok”,opt 3 margin=“ok”,opt 4 margin=“ok”,opt 5 margin<>”ok”),max(opt1 price,opt2,opt3,opt4), if(and($A$1=“Max”,option 1 margin=“ok”),opt1, if(and($A$1=“Max”,opt 2 margin=“ok”),opt2, if(and($A$1=“Max”,opt 3 margin=“ok”),opt3, if(and($A$1=“Max”,,option 4margin=“ok”),opt4, if(and($A$1=“Max”,option 5margin=“ok”),opt5,”do not include”

 

[jasonb75]

I'm not even going to attempt to dissect that based on a description alone. It is quite clearly not your exact current formula because it contains invalid syntax.

I'll take 1 guess based on a glance at the above, if you need more help then please post a small data sample with the proper formula using XL2BB

=IFERROR(SMALL(IF(CHOOSE({1,2,3,4,5},Margin1,Margin2,Margin3,Margin4,Margin5)="ok",CHOOSE({1,2,3,4,5},Price1,Price2,Price3,Price4,Price5)),IF($A$1="Min",1,5)),"Do not include")

Array confirm with Ctrl Shift Enter if you are using anything other than office 365!