Chart historical - Y-T-D

[Rustyf2003]

Hello to All:
Have been bouncing around this and still can't accomplish
=Comparing YTD historical data
Tracking church offering by week - historical data goes back to 2005

Sheet
Column A = Week
Coulumn B = 2011
Column C = 2010
Column D = 2009
Column E = 2008
Column F = 2007.........................

Row 2 through Row 54 includes week numbers 1-53 respectively

As of Today - Saturday, March 5 = Column B has data in row 2 through row 9
Columns C thorough Columns I have those years week rows complete

I HAVE USED Sum(OFFSET to correctly give me the cummulative total of offerings received for each year to THIS POINT IN TIME.

Ideally, I would like to demonstrate the weekly offerings YTD in a chart
- BUT to this point, I haven't had success.

FYI - I don't really know what I am doing, so I do appreciate and look forward to any assistance offered.

Off now, to get some rest.
Rusty

 

[energman58]

You need to create some dynamically named ranges for teh data you are interested in.

Say a range called YTD which would have the formula in the define names dialog box that looks like this

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

And a range called Date which would have the formula

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

you could also plot last year as a comparitor with a range

= OFFSET (Sheet1!$C$1,1,0,COUNTA($B:$B)-1)

Then you just build a chart as you would normally - line chart or whatever - but then select the two series in turn and replace the references to the data in the SERIES formula (which appears in teh formula bar when you select a chart series) with references to the named ranges. This way when you add another week's taking to column B all the ranges expand by one row.

The syntax for Series is

Series(Name of the series - appears in the legend for example "Year To Date", Category (X Axis) Values in your case DATE, Values - in your case YTD or whatever, Plot order a sequential number which tells Excel what order the series plot - you dont need to change this)

This assumes that the rest of column B is empty - if there is data below just modify the range in the COUNTA part of the expression to read B1:B53 or whatever.

 

[Rustyf2003]

Energman58 - Thank you for the reply and the assistance provided that helped me accomplished what I was in need of.

In the end I decided to create the chart to compare this years data to the same period of time for the preceding 4 years.
In accomplishing this:
I made 5 series utilizing dynamic range names
and qualified the range by Column with the Offset function

Identified the series name to also be dynamic using the respective column heading.

Again, Thanks for your assistance.
Rusty

 

[energman58]

You are welcome