Link to home
Start Free TrialLog in
Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland

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(POWER(ABS($O13-AI$10),2)+POWER(ABS($P13-AI$11),2))/Metres)),FALSE)

And I want to NOT have to include the constraints part:
IF(AND(+Lwr<($H13-AI$8),+Upr>($H13-AI$8))
so that it reads thus:
=((SQRT(POWER(ABS($O13-AI$10),2)+POWER(ABS($P13-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
ASKER CERTIFIED SOLUTION
Avatar of Anthony Mellor
Anthony Mellor
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Anthony Mellor

ASKER

no answers, so I have added an aide memoire to indicate how I solved the problem in the end.