Anthony Mellor
asked on
How to select one cell from a number of columns, based on different criteria per column? Minif?
In the attached example file:
ROW 13 is the current formula in full from cell AI13
ROW 14 onwards is the formula without the constraint (so all the FALSEs disappear)
AT Data sheet AI13 formula now reads:
=IF(AND(+Lwr<($H13-AI$8),+ Upr>($H13- AI$8)),--( (SQRT(POWE R(ABS($O13 -AI$10),2) +POWER(ABS ($P13-AI$1 1),2))/Met res)),FALS E)
And I want to NOT have to include the constraints part:
IF(AND(+Lwr<($H13-AI$8),+U pr>($H13-A I$8))
so that it reads thus:
=((SQRT(POWER(ABS($O13-AI$ 10),2)+POW ER(ABS($P1 3-AI$11),2 ))/Metres) )
To achieve that I need to be able to give effect to the constraints (i.e. between 59 and -84) within the column selection formula in column Q which currently reads:
=MIN(AI13:BB13) This works when FALSE is applied to any column outside the constraints.
Office 365 Excel Version 15.32 Mac OSX Current install MacPro.
This is a follow on question from here, which was to prove the idea can work in Excel:
https://www.experts-exchange.com/questions/28998243/Compare-3-000cells-with-each-one-of-500-000-cells-how.html?anchor=a41999024¬ificationFollowed=183829737&anchorAnswerId=41999024#a41999024
SiteTest9907-HOMECORNER-Named.xlsx
ROW 13 is the current formula in full from cell AI13
ROW 14 onwards is the formula without the constraint (so all the FALSEs disappear)
AT Data sheet AI13 formula now reads:
=IF(AND(+Lwr<($H13-AI$8),+
And I want to NOT have to include the constraints part:
IF(AND(+Lwr<($H13-AI$8),+U
so that it reads thus:
=((SQRT(POWER(ABS($O13-AI$
To achieve that I need to be able to give effect to the constraints (i.e. between 59 and -84) within the column selection formula in column Q which currently reads:
=MIN(AI13:BB13) This works when FALSE is applied to any column outside the constraints.
Office 365 Excel Version 15.32 Mac OSX Current install MacPro.
This is a follow on question from here, which was to prove the idea can work in Excel:
https://www.experts-exchange.com/questions/28998243/Compare-3-000cells-with-each-one-of-500-000-cells-how.html?anchor=a41999024¬ificationFollowed=183829737&anchorAnswerId=41999024#a41999024
SiteTest9907-HOMECORNER-Named.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER