UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> MaxIf - MinIf AverageIf    
MaxIf - MinIf AverageIf

MaxIf – MinIf - AverageIf

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

image:Weather_Data.jpg

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.

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 2,364 times.  This page was last modified 23:11, 18 February 2015 by dflak.   Disclaimers