• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for MAX value of cell range

Ashwini

New Member
Hi All,
In the attached workbook we have the range of values in A2:J168. In Col M, I have the dates. For each date, I need to find the MAX value in Col J for the rows that correspond to that date. For example, '10-01-06' has 24 rows of data from Row 2 through Row 25. I need to get the MAX value of the values in Col J.
I worked out a vba code which will do the job. But I am looking for a formula (maybe using INDEX, MATCH, AGGREGATE etc) that can be entered in Col N corresponding to the Date entries in Col M.
Please help.
Ashwin
 

Attachments

  • TestData.xlsx
    24.7 KB · Views: 8
Here are some initial choices of formula
= MAXIFS(Table1[Combined Avg Load], Table1[Date], date)
= MAX( IF(Table1[Date]=date, Table1[Combined Avg Load] ) )

and, dynamic array versions of Excel only,
= MAX( FILTER( Table1[Combined Avg Load], Table1[Date]=date) )

It would also be possible to use the fact that the dates are sorted to perform a bisection search for the first and last matching record, then build a range and use MAX().
 
Hello Peter,
Thanks for the reply.
I tried the second formula: =MAX(IF(Table2[Date]=M5,Table2[Combined Avg Load]))
I am getting a value of 451.6091 which is not a value in the range for Combined Avg Load Col J for 10-01-06.
Before that I converted the range of the Cols A to J as Table to make use of the formulas.
I am not seeing the function MAXIFS in my Office 2016 version.
The array formula does not work either.

Krishna
 
1] The MAX+IF formula is an array formula, you must confirmed to press SHIFT+CTRL+ENTER 3 keystrokes together instead of just ENTER.

2] MAXIFS and FILTER are new functions for the newly Office 365 subscribed users only and not available in Excel 2016 or below.

3] Or, your can try this non-array formula instead :

=AGGREGATE(14,6,J$2:J$168/(B$2:B$168=M5),1)

Regards
Bosco
 
Last edited:
Bosco

Thanks for sorting this. I should have been more careful to spell out the options. For me, having made the move from Office 2010 to 365 insider especially to gain access to dynamic arrays, the tricks required to avoid implicit intersection are fading from memory. I knew MAXIFS was a late addition but I, wrongly, thought it had appeared after the Office 2013 release.

Since I was indicating alternative formulae, I should have remembered
= AGGREGATE( 14, 6, Table1[Combined] / (Table1[Date]=Date), 1 )
I find it somewhat curious that the more obvious reference form of the function
= AGGREGATE( 4, 6, Table1[Combined] / (Table1[Date]=Date) )
fails with or without CSE.

In Excel 2010, one of my main strategies was to formulate all array formulas as Named Formulas so that the Name 'filtered' that refers to
= IF( Table1[Date]=Date, Table1[Combined] )
could then be used with a grid formula
= MAX( Filtered )
without any need for CSE.
 
Hello Bosco,

Thank you. The formula worked. You saved me a lot of research.
And Peter, thank you too for some valuable tips.

Krishna
 
Bosco
I find it somewhat curious that the more obvious reference form of the function
= AGGREGATE( 4, 6, Table1[Combined] / (Table1[Date]=Date) )
fails with or without CSE.
It's explained in the inner help that aggregate can accept arrays for only a limited number of aggregate types. LARGE/SMALL will work, but MAX/MIN not.
 
Back
Top