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))
Search JabSto ::

Custom Search