MAX array upto latest entry

[lukman4068]

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]

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]

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]

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]

Try this formula. Just copy and paste is straight in I tried to build it for your requirements.

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