Persistent Minimums And Maximums
This page describes how to write formulas for persistent
minimums and maximums using Circular References.
You can very simply find the minimum and maximum number in a range by using the
MIN and MAX functions.
However, these functions cannot provide you with a persistent minimum or
maximum; that is, the minimum or maximum value that has ever occurred in the range.
For example, if at one point the maximum in the range is 100 and then later the
maximum is 80, the persistent maximum is 100, even though that value is no
longer in the range.
Using Circular References, you can create formulas to work with persistent
minimums and maximums.
CIRCULAR REFERENCE: A circular reference is a formula that refers to its own
cell in the formula. For example, a formula in cell A1 that includes A1 in the
formula is a circular reference. A circular reference may be direct, in which case a formula references
its own cell, or it may be indirect, in which case a formula refers to a cell
that refers back to the original cell. If A1 references B1, and B1 references
C1, and C1 references A1, then A1 has an indirect circular reference. To enable
circular references, go to the Tools menu, choose Options, then the Calculation
tab. There, check the Iteration checkbox.
Suppose your data is in the cells B11:B15. To
calculate the persistent maximum, enter the following formula in cell
E11:
=IF(E11<=MAX(B11:B15),MAX(B11:B15),E11)
To calculate the persistent minimum, enter the following in cell
E12:
=MIN(B11:B15,IF(E12=0,MIN(B11:B15),E12))
Note that if you edit one the formulas (e.g.,
F2 in the cell), it will be reset to the minimum or maximum of
the current values in the range and the persisted value will be lost.
This page last updated:
19-July-2007