Excel/Array MaxIf

Advertisement


Question
QUESTION: I'm not sure what I"m doing wrong here, any advice is appreciated.

=MAX(IF($D:$D,"<"&$AY$37))
hit ctrl, shift enter and it didn't calculate the max given that specification.  Am I doing something incorrectly?

ANSWER: Bill,

If you want the max value in column D considering only values that are less than the value in AY37 then it would be


=MAX(IF(($D:$D<$AY$37),$D:$D))  Entered with Ctrl+Shift+enter

Look at the formula closely because I made some major changes to it.


I assume you are using excel 2007 or later because earlier versions do not support using and entire column in an array formula.

--
Regards,
Tom Ogilvy




---------- FOLLOW-UP ----------

QUESTION: Sorry Tom, I looked at it again and it's not quite returning what I'd hoped.  Each row is one unique name.  For all rows in which Column D is less than AY7, I'd like to return the max value in column AL.

I tried changing this to =MAX(IF(($D:$D<$AY$37),$AL:$AL))  Entered with Ctrl+Shift+enter, but that just returned the absolute max for all of column AL.

Answer
Bill,

What you did is correct if AL contains the numbers.

The comparison is the only point of concern.

You said each row is a unique name, but what is being compared in Column D and AY37  (the formula shows AY37 as per your first question, but here you say AY7 (could be a typo).

Anyway, if column D and is a number and AY37 contains a number, for that row, if the number is less than the number in AY37, then the number in column AL will be returned and if not, then FALSE will be returned.  The MAX function will ignore FALSE.  

Now if you have blanks in column D, and it is a numerical comparison, those could be seen as less than the number in AY37.   This will correct for that

=MAX(IF(($D:$D<$AY$37)*LEN(TRIM($D:$D)),$AL:$AL))


but the formula should work (and that was tested as one without the extra condition and it worked for me.

If you want to send me the workbook and show where you think you are getting the wrong answer, I will take a look.

Send to:
twogilvy@msn.com

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.