I'm not very good with Excel at all and I haven't found a specific answer for this. I'm looking for a simple formula to retrieve the max value in a particular column. I need it to begin searching from row 8, so for example =MAX(A8:"?"). I thought it would be * or something like that but it doesn't appear to be. I don't know what the last row is going to be because it will vary. How do I specify the 2nd variable in the formula to be the last row in the column without knowing beforehand?
4 Answers
Try creating a "Dynamic named range". If you have continuous numeric data from A8 downwards then that can be defined as
=OFFSET($A$8,0,0,COUNT($A$8:$A$1048576))
[Excel will add in sheet names]
If you name that range Data then you can simply use
=MAX(Data)
The DNR may also come in useful for other calculations
In Excel 2007 or later versions You could also convert your data in to a table and then you can refer to a specific column of the table which will increase in size as the size of the table increases.
Some information on Dynamic named ranges here: http://www.contextures.com/xlNames01.html
answered Dec 7, 2012 at 13:08
Similar to Barry's solution, but a little cleaner (in my opinion).
=MAX(OFFSET($A:$A,7,0,COUNT($A:$A)))
It's ugly, but I'd use: =MAX(A8:A1048576)
-
Haha yeah I'm looking for ways to avoid this. I didn't think it'd be such a hassle to do something like this with such and advanced application. If I can't find a better solution I'll have to go with this.– user1066133Dec 7, 2012 at 12:32
Simple way in Excel:
=MAX(A8:A1048576)
Creating UDF in VBA. This allows for a dynamic offset and will sum the column it is placed in:
Function SumColumn(offset As Double)
Dim Data As Range
Set Data = Application.Caller.EntireColumn.Resize(Application.Caller.EntireColumn.Rows.Count - offset, 1).offset(offset, 0)
SumColumn = Application.WorksheetFunction.Sum(Data)
End Function
