Question: I have a list that I add data to every day. I would like to find the largest number in that growing list dynamically?
Formula in E3: =MAX(OFFSET($A$4, 0, 0, MAX(IF(ISNUMBER(A:A), ROW(A:A)-3, 0)), 1)) + CTRL + SHIFT + ENTER
How to find the smallest value:
=MIN(OFFSET($A$4, 0, 0, MAX(IF(ISNUMBER(A:A), ROW(A:A)-3, 0)), 1)) + CTRL + SHIFT + ENTER
Now what if there is a blank cell somewhere in the list? It doesn´t matter, the cell range expands dynamically to the last number anyway.
The above formulas can be time consuming, specially if you have an older computer. It can be wise to switch to manual calculation if you experience this problem.
Download excel example file
(Excel 97-2003 Workbook *.xls)
Functions used in this blog post:
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
ROW(reference) returns the rownumber of a reference
Returns the smallest number in a set of values. Ignores logical values and text
Returns the largest value in a set of values. Ignores logical values and text.
Returns a reference to a range that is a given number of rows and columns from a given reference
Checks whether a value is a number and returns TRUE or FALSE