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?

Answer:

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-finding-the-largest-number-in-a-dynamic-column.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

IF(logical_test;[value_if:true];[value_if_false])
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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE