- To post as a guest, your comment is unpublished.· 10 months agoHi, JPE, try to change 1000 to larger number in the formula. Such as =MAX(OFFSET(B1,2,0,2000))
How to find the maximum value in a dynamic Excel column?
To find the maximum value in a range is easy for most Excel users, but do you know how to find the maximum value in a dynamic column? In other words, the maximum value will change as the values changed or deleted or added in the column. Now I introduce some formulas to handle this job in Excel.
Here are two formulas that can help you to find the maximum value in a dynamic column in two different cases.
If you want to place the maximum value in another column, you can do as this:
Select a blank cell you will place the maximum value in, says Cell M1, and type this formula =MAX(INDEX(L:L,3):INDEX(L:L,MATCH(99^99,L:L,1))), L is the Column letter you use, press Enter key to get the result.
If you want to place the maximum under a column header and above the data value as below screenshot shown, you can do as this:
Select the cell you want to put the maximum value, type this formula =MAX(OFFSET(B1,2,0,1000)), and press Enter key. See screenshot:
Note: B1 is the cell above the formula cell, 2 means to find maximum value from the second cell below header cell to the end if you want to find maximum value from the third cell below to end, change 2 to 3. See screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 10 months agoSorry but it only gives us the max of the first 1000 rows. How to have the max of a whole column even if I add some rows