MaxIf - MinIf AverageIf

MaxIf – MinIf - AverageIf

Suppose you have 115 years of weather data organized like the following:

You want to find the record high and low for a given date, say for January 2nd. All January 2nds in this data set have a month – day of 01-02. What you need to do is look at only records with 01-02 as the month - day and check for the Max (High) and Min (Low).

You need a way to tell Max and Min to ignore the other data. This can be accomplished with an array formula.

The “generic” formula for this type of operation is
=Function(IF(LookupRange=LookupValue,IF(NOT(ISBLANK(ValueRange)),ValueRange,False)))

- Lookup Range is the range on which you look up. In this case, it is the Month – Day Column.
- Lookup Value is the value you seek. In this case, 01-02.
- Value Range is the range to which the formula is to be applied. The High Column for Max and the Low Column for Min.

Array formulas have on basic requirement: the ranges must be of the same size. In this case n is the last row in our data set.

To get the Maximum High use:
=Max(IF($B$2:$B$n=”01-02”,IF(NOT(ISBLANK($C$2:$C$n)),$C2:$C$n,False)))

To get the Minimum Low use:
=Min(IF($B$2:$B$n=”01-02”,IF(NOT(ISBLANK($D$2:$D$n)),$D2:$D$n,False)))

The formula can also be used for other aggregates such as Average or StdDev. It could also be used for SUM, but SUMIFS is built-in and is more intuitive.