Content
Resources
To Do
Toolbox

 MaxIf - MinIf AverageIf
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.