## Find the largest number in an expanding cell range

*Article updated on January 17, 2018*

**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 fil****e**

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

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]Filter unique distinct values if value contains specific string [Formula and Advanced Filter]

Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]Quickly create links to sheets, tables, pivot tables and named ranges in a workbook

A week ago I posted Create links to all sheets in a workbook and today I want to show you how […]List all named ranges and their cell references in a worksheet

This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]### One Response to “Find the largest number in an expanding cell range”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I always spent my half an hour to read this website's articles or reviews every day along with

a mug of coffee.