Microsoft Office Tutorials and References

In Depth Information

The formulas that return the values shown in rows 2 and 3 in the

figure reference the final numbers calculated elsewhere. The

formulas merely return that data for easy use.

Calculate Maximum and Minimum Values

Formulas in rows 4 and 5

return the maximum and

minimum values of the data

found in rows 2 and 3.

AB

C

D

E

F

1

2

3

4

5

Mar-04

Jun-04 Sep-04 Dec-04

Firm 1

2

4

3

6

Firm 2

3

1

4

2

Max

3

4

4

6

Min

2

1

3

2

To illustrate, the formula in cell C4 is merely =MAX(C2:C3).

However, if the source data could contain error values (including

#N/A values), the formulas in rows 4 and 5 must be written to

ignore such errors.

Here’s a formula for cell C4, a formula that will find the maximum or

minimum values for all cells that don’t contain errors:

=MAX(IF(NOT(ISERROR(C$2:C$3)),C$2:C$3))

This formula must be
array entered
. That is, after you type the

formula into the formula bar, hold down your Ctrl and Shift keys

before you press Enter. After you enter this formula, it will be

enclosed in braces, like this: “{..….}”

When you use the array formula, Excel creates a temporary column

of numbers in memory, a column consisting only of those numbers

that satisfied your criteria, and then Excel returns the maximum of

those numbers.

Similarly, the array formula in cell C5 is:

=MIN(IF(NOT(ISERROR(C$2:C$3)),C$2:C$3))