Excel formula: Max if criteria match

WRITTEN BY: supportmymoto.com STAFF

The instance proven accommodates virtually 10,000 rows of knowledge. The info represents temperature readings taken each 2 minutes over a interval of days. For any given date (offered in cell H7), we need to get the utmost temperature on that date.

Contained in the IF operate, logical check is entered as B5:B9391=H7. As a result of we’re evaluating the worth in H7 towards a variety of cells (an array), the end result will likely be an array of outcomes, the place every merchandise within the array is both TRUE or FALSE. The TRUE values signify dates that match H7.

For the worth if true, we offer the vary E5:E9391, which fetches all the complete set of temperatures in Fahrenheit. This returns an array of values the identical measurement as the primary array.

The IF operate acts as a filter. As a result of we offer IF with an array for the logical check, IF returns an array of outcomes. The place the date matches H7, the array accommodates a temperature worth. In all different instances, the array accommodates FALSE. In different phrases, solely temperatures related to the date in H7 survive the journey via the IF operate.

The array end result from the IF operate is delivered on to the MAX operate, which returns the utmost worth within the array.

With MAXIFS

In Excel O365 and Excel 2019, the brand new MAXIFS operate can discover the utmost worth with a number of standards with out the necessity for an array formulation. With MAXIFS, the equal formulation for the this instance is:


NOTE : Please do not copy - https://supportmymoto.com
See also  Excel formula: Convert date to month and year

Leave a Reply

Situs Judi Slot Online Terpercaya

Link Slot Gacor

Slot Anti Rungkat

Slot Gacor Maxwin