» Finding the Largest Value from Two Different Lists, Subject to Specified Criteria
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Columns A & B contain two lists of numbers.
We want to create a formula that will return the maximum value from either list, providing that the following two conditions are satisfied:
Numbers from List1 must be larger than 3.
Numbers from List2 must be larger than 20.
If the maximum value found does not meet the above criteria, the formula should look for the next largest number until finding the largest number matching criteria.
Solution:
Use the MAX functions in the following Array Formula:
{=MAX(MAX((A2:A5>C2)*A2:A5),MAX((B2:B5>C3)*B2:B5))}
Book Store:
Recommended Books:
- Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
- Infectious Greed: How Deceit and Risk Corrupted the Financial Markets
- The Analysis and Use of Financial Statements
- Microsoft Excel 2002 Simply Visual
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
- Monte Carlo Methods in Finance
should you avoid array formulas if non-array formulas will do the job?
dnickelson
This could be done with =MAX(IF(MAX(A:A)>C3,MAX(A:A),0),IF(MAX(B:B,)>C4,MAX(B:B),0)) without the need for using an array formula.
