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

Related posts:

1. Identify largest text value in a column using array formula in excel
2. Extract largest values from two columns using array formula in excel
3. Largest value in a range using date criteria in excel
4. Create a list of all numbers or text in a column in excel
5. Sort text values by length using array formula in excel
6. Extract numbers and text from a range using array formula in excel
7. Unique distinct list from a column sorted A to Z using array formula in excel
8. Highlight duplicate values in two ranges combined using conditional formatting in excel
9. Highlight unique distinct values in two ranges combined using conditional formatting in excel
10. Min and max unique and duplicate values using array formula in excel