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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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,209
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,136,328
Messages
5,675,137
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top