Excel Guides

Deriving High and Low Non-Zero Values in Excel

When working with data in Excel, it is often necessary to identify the highest and lowest values in a given range. This can be accomplished using the MAX and MIN functions, respectively. However, sometimes there are zero values mixed in with the non-zero data, and it is necessary to exclude these zeros when finding the extreme values. This can be done by adding a simple criterion to the MAX and MIN functions.

For example, suppose we have a range of data in cells A1:A10, and we want to find the highest non-zero value. We can use the following formula:

=MAX(A1:A10)*(A1:A10<>0)

The <>0 part of the formula will ensure that only non-zero values are considered by the MAX function. We can use a similar approach to find the lowest non-zero value in the range:

=MIN(A1:A10)*(A1:A10<>0)

It is also possible to find both the highest and lowest non-zero values in a single formula using the IF function. For example, the following formula will return the highest non-zero value in cells A1:A10:

=IF(MAX(A1:A10)=0,MIN(A1:A10),MAX(A1:A10))

If there are no zeros in the range, then this formula will simply return the result of the MAX function. However, if there are one or more zeros present, then the formula will return the result of the MIN function instead.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.