Multiple criteria to choose Min or Max

Caly

Board Regular
Joined
Jul 19, 2015
Messages
160
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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”
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
Excel Formula:
=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!
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

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