max formula based on rolling date range

[weehawken]

Hi all,


I need to create max formula based on a dynamic data range eg the data set will continue to grow over time (WC 1, 2 3, 27, 28 etc) but I am unable to find a function to find the end of the range. Is it possible to do without creating a macro?


WK1 WK2 WK3 WK8 WK9 WK10 WK30 =MAX()

Metric A

Metric B


Thanks in advance

 

[Luke M]

Check out Chandoo article here where he creates a dynamic range:

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


you'll probably set something up like:

=MAX(OFFSET($A$2,0,0,1,COUNTA(1:1)))

 

[Hui]

Weehawken


Firstly, Welcome to the Chandoo.org forums.


You can use the offset function to return a moveable date range

It will be something like: =Max(Offset(Reference cell, Row Offset, Column Offset, Row Size, Column Size)

You may want to read more about this at: http://chandoo.org/wp/2012/09/17/offset-formula-explained/

 

[majay1973]

Hi weehawken,


Could you attach a sample file.. that will be lot easier to understand your request.


however, for stand alone point of getting the dynamic data range, you could use defining a name with offset formula...


=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))


The offset formula will take care of any data added below the last row or any data added after the last column.


Hope this helps...


Have a great day..

 

[weehawken]

Hi, thanks for your feedback. with a bit of tweaking I got the formula to work.