Formula for Calendar Year to Date (CYTD)
 

I was looking on here for a formula that ascertains the values
pertaining to the CYTD and finally came up with my own solution that I
post here for others. I don't want a formula that stops at the end of
a calendar year and restarts at the start of the next. With this
solution, you can copy the formula down the sheet over multiple years.

* I have a column of dates (end of month).
* I have a relative named range called Date that refers to the current
month. If the dates are in column A and the cursor is in cell D40
then the named range Date refers to $A40.
* I created a named range called CYTD which refers to "=OFFSET(Date, -
(MONTH(Date)-1), 0, MONTH(Date))"
* Now, if I want the sum of values in column B as running CYTD values,
my formula will be =SUM(OFFSET(CYTD, 0, 1))
* Or if I want the rate of the value in column B divided by the value
in column C, it'll be =SUM(OFFSET(CYTD, 0, 1))/SUM(OFFSET(CYTD, 0, 2))

The main issue is defining which range defines the CYTD. Once you
have that, any other calculation based on it is easily ascertained by
using the relevant offset.

Paul Martin
Melbourne, Australia