Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 7 '12 at 14:28

This question came from our site for professional and enthusiast programmers.

    
This solution locates the value of the last cell in the column which is partly what I need but I'm not quite sure how to dissect it to get just the last non-empty cell location within the column. –  user1066133 Dec 7 '12 at 12:35
add comment

4 Answers

Similar to Barry's solution, but a little cleaner (in my opinion).

=MAX(OFFSET($A:$A,7,0,COUNT($A:$A)))
share|improve this answer
add comment

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
share|improve this answer
add comment

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

share|improve this answer
add comment

It's ugly, but I'd use: =MAX(A8:A1048576)

share|improve this answer
    
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. –  user1066133 Dec 7 '12 at 12:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.