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