# Chart historical - Y-T-D

#### Rustyf2003

##### New Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### energman58

##### Well-known Member
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.

Last edited:

#### Rustyf2003

##### New Member
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.

Rusty

You are welcome

Replies
2
Views
494
Replies
8
Views
2K
Replies
5
Views
814
Replies
0
Views
404
Replies
2
Views
770

1,191,501
Messages
5,986,922
Members
440,067
Latest member
Swatts1

### 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.

### Which adblocker are you using?

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

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