Summary

To calculate the max or min change in a set of data as shown, without using a helper column, you can use an array formula. In the example, the formula in G6 is:

=MAX(C5:C12-D5:D12)

The result is the maximum daily change in the data as shown.

Generic formula

=MAX(rng1-rng2)

Explanation

The example on this page shows a simple array formula. Working from the inside out, the expression:

C5:C12-D5:D12

Results in an array containing seven values:

{17;19;32;25;12;26;29;22}

Each number in the array is the result of subtracting the "low" from the "high" in each of the seven rows of data. This array is returned to the MAX function:

=MAX({17;19;32;25;12;26;29;22})

And MAX returns the maximum value in the array, which is 32.

MIN change

To return the minimum change in the data, you can substitute the MIN function for the MAX function:

=MIN(C5:C12-D5:D12)

As before, this is an array formula and must be entered with control + shift + enter.

More on arrays

To understand array formulas, you must learn to investigate the results of various operations inside a formula as it is being evaluated by Excel. In particular, you need to know how to use the F9 key to debug a formula. See this article for an overview: Arrays in Excel.

Video: How to check and debug a formula with F9 (3 min, free). It's one of 117 lessons in our Core Formula course.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.