MAX array upto latest entry

lukman4068

New Member
Joined
Jun 15, 2009
Messages
24
Hi,

Here's my question -

I can't set my table as an excel table or "list" to allow for a dynamic range that way.

For this month, I can return the max value using the MAX formula as it is:
=MAX(F4:F16)

What i'd like to happen is for the F16 to change to F17 automatically when the new row is added. This could also be driven by a new month as the data is updated a monthly basis.

Hopefully you understand the question and what I'm trying to do.

I tried something like

=MAX(F4:&"F"&D1)

D1 is where I put the number of rows down I wanted the array to go from the start cell (F4)

I tried that to try and string it together :/ But it obviously doesn't work.

Appreciate the help in advance as ever!
 

TheIAAway

New Member
Joined
Feb 22, 2010
Messages
28
Why not just extend your formula to account for as many cells as you want now? Is there any data that lives below this section?
 

lukman4068

New Member
Joined
Jun 15, 2009
Messages
24
Not at the moment, but there may be later on - I know the alternative is a "running" max column which you could add on but there are already 15 columns of data so I don't really want that to double to 30 columns by adding a new running max column.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Hi,

Here's my question -

I can't set my table as an excel table or "list" to allow for a dynamic range that way.

For this month, I can return the max value using the MAX formula as it is:
=MAX(F4:F16)

What i'd like to happen is for the F16 to change to F17 automatically when the new row is added. This could also be driven by a new month as the data is updated a monthly basis.

Hopefully you understand the question and what I'm trying to do.

I tried something like

=MAX(F4:&"F"&D1)

D1 is where I put the number of rows down I wanted the array to go from the start cell (F4)

I tried that to try and string it together :/ But it obviously doesn't work.

Appreciate the help in advance as ever!
If you are on Excel 2003, convert into list by means Data|List|Create List. On Excel 2007 or beyond, look at the Table feature, similar to the List feature on Excel 2003.

Otherwise, you can do the following:

=MAX(OFFSET(F4,0,0,MATCH(9.99999999999999E+307,F:F)-SUM(ROW(F4))+1))
 

TheIAAway

New Member
Joined
Feb 22, 2010
Messages
28
Try this formula. Just copy and paste is straight in I tried to build it for your requirements.

Code:
=MAX(F4:INDEX(F:F,MATCH(9.99999999999999E+307,F:F)))
 

Forum statistics

Threads
1,082,018
Messages
5,362,702
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top