Excel 2013

Question

Is there a MAX and MIN function that works like AVERAGEIF?  Looking to find the max and min values in a database based on specific criteria.

Answer 1

Hello,

not in Excel 2013 and earlier. The Office 365 edition of Excel 2016 has new functions MAXIFS and MINIFS, which also work in Excel online, but for Excel 2013 on the desktop you will need to use an array function that nests an IF into the MAX function.

Consider the following screenshot:

The formula in E2 is =MAX(IF($A$2:$A$13=D2,$B$2:$B$13)) and in F2 is =MIN(IF($A$2:$A$13=D2,$B$2:$B$13))

These are array formulas and must be confirmed with Ctrl-Shift-Enter. Excel will then put the curly braces in. You don't type these.

Answer 2

Hi Travis62,

Is there a MAX and MIN function that works like AVERAGEIF?  Looking to find the max and min values in a database based on specific criteria.

Try formulas of the type::

      =MAX(IF(A1:A1000="Jan",B1:B1000))   

      =MIN(IF(A1:A1000="Jan",B1:B1000))   

Theses are array formulas and should be confirmed with CTRL+Shift+Enter rather than Enter.

===

Regards,

Norman

Answer 3

hit reply on wrong comment