Find the largest number in a dynamic column using excel formula
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:
- Identify largest text value in a column using array formula in excel
- Extract largest values from two columns using array formula in excel
- Largest value in a range using date criteria in excel
- Create a list of all numbers or text in a column in excel
- Sort text values by length using array formula in excel
- Extract numbers and text from a range using array formula in excel
- Unique distinct list from a column sorted A to Z using array formula in excel
- Highlight duplicate values in two ranges combined using conditional formatting in excel
- Highlight unique distinct values in two ranges combined using conditional formatting in excel
- Min and max unique and duplicate values using array formula in excel



Leave a Reply