Year to Date Formula

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

I need to report on year to date (YTD) versus last year's
YTD figure.

Spreadsheet is set up as follows:
A1 = Report Month
A2:AJ2 = Month headers (Jul-02 through Jun-04)
A3:AJ3 = data

I want 2 results cells, the first one is last year YTD
where
If A1 = Jul-03,sum(A3:A3)
If A1 = Aug-03,sum(A3:B3)
If A1 = Sept-03,sum(A3:C3)
....and so on
The second results cell, for this year YTD
If A1 = Jul-03,sum(Y3:Y3)
If A1 = Aug-03,sum(Y3:Z3)
If A1 = Sept-03,sum(Y3:AA3)
....and so on

Is there an automated way to do this so that I don't have
to change the formula references each month?

Thanks
Sarah
 
Hi

I modified your design a bit. You can use it, or when you like your own
design better, then you modify the formula.

A1=Start month for reporting period
B1=End month for reporting period
The
sum==SUM(OFFSET(A$3,,MATCH($A$1,$A$2:$AJ$2,0)-1,,MATCH($B$1,$A$2:$AJ$2,0)-MA
TCH($A$1,$A$2:$AJ$2,0)+1))


Arvi Laanemets
 
An afterthought:
The formula will be much simpler, when you give in cell B1 the length of
reporting period in months instead of ending month

=SUM(OFFSET(A$3,,MATCH($A$1,$A$2:$AJ$2,0)-1,,$B$1))

Arvi Laanemets
 
For last year's YTD:
=SUM(OFFSET($A$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))

For this year's YTD:
=SUM(OFFSET($Y$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))
 
Ah, but now you're making her do the work to calculate the number of months <g>. She wanted
Excel to do that, given the date in A1. For a fiscal year that begins in July,
MOD(MONTH($A$1)-7,12)+1 gives the number of months. For fiscal years that begin in a different
month, if the first month of the year is in, say, A2,

MOD(MONTH($A$1)-MONTH($A$2),12)+1

gives the number of months from $A$2 through $A$1, inclusive.
 
Thanks for all your assistance everyone (I should have
mentioned that the financial years run July to June here
in Australia).

Now the $1 million question...
Is there a way to sum every-other cell similar to below,
where A2:AJ2 are the month headers merged across 2 columns
(A2 and B2 = July 02) and the row 3 data alternates
budget, actual, budget actual (A3 = budget for July 02, B3
= actuals for July 02, c3 = budget for Aug 02...etc)

In other words, another result I am looking for is
If A1 = Jul-03,sum(A3)
If A1 = Aug-03,sum(A3,c3)
If A1 = Sept-03,sum(A3,e3)

Or should I just count my blessings and reformat my
spreadsheet to work with the sum/offset/month formulas
already provided?

Thanks again
Sarah
 
Myrna: I'm trying to apply the formula to a spreadsheet but it is not working. Can I email you my spreadsheet so you can take a look at it? If so, please email me ([email protected]) so I can reply you with the attachment

Thanks in advance
----- Myrna Larson wrote: ----

For last year's YTD
=SUM(OFFSET($A$3,0,0,1,MOD(MONTH($A$1)-7,12)+1)

For this year's YTD
=SUM(OFFSET($Y$3,0,0,1,MOD(MONTH($A$1)-7,12)+1)
 
I need a formula that calculates Year-To-Date figures automatically, without editing the existing formula each month. My table is divided in months (December 2003 to November 2004). Each month has three columns: 1) budget, 2) Actual figures, and 3)Variation from budget. I thought this was going to be easy but I've been working in it for a week without good results. I need a formula that can calculate automatically year to date figures. It is important to say that budget figures changes every other month.

The composition of the table is as follows:
1. In cell A6 is the current date [=today()] (MONTH ONLY)
2. Data is on cells e10:av10 (There are 46 rows in the table, but figuring out how it works in one row will make the remaining)
3. YTD Results are in cells BA10:BC10 [Budget, Actual, Variation (one column for each result)]
4. 6 months sub totals are in cells x10:z10 (these figures are the sum of the first 6 months)

Since I speak english as a second language, I really hope that you can understand what I need. Please let me know if you need any further information.

----- Rafa Zorrilla wrote: -----

Myrna: I'm trying to apply the formula to a spreadsheet but it is not working. Can I email you my spreadsheet so you can take a look at it? If so, please email me ([email protected]) so I can reply you with the attachment.

Thanks in advance!
----- Myrna Larson wrote: -----

For last year's YTD:
=SUM(OFFSET($A$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))

For this year's YTD:
=SUM(OFFSET($Y$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))
 
The formula I provided for Sarah has the data for a given month in 1 column,
3 rows. You have it side-by-side. So the first formula will definitely not
work.

You say the data goes from December through November. Are you dealing with a
fiscal year that starts in December rather than a calendar year that starts
in January? If so, you can't use the month number directly to determine how
many cells to include. BTW, if A6 contains the formula =TODAY(), it contains
a complete date, with day, month, and year, even though you have chosen to
display only the month.

You say there are 3 columns for each month, and the data is in E10:AV10.
That is 44 columns, not 36, so I'm not sure of your layout at all.

Anyway, here's a macro that will sum every third column in the first row of
a HORIZONTAL range, beginning with the 1st cell in the range, and including
the number of values (i.e. the number of months) specified by NumToSum.

So if you want to do the budget figures, which are in the 1st, 4th, 7th, etc
columns of the block, and you want to do the first 8 months, the formula is

=SumEveryThirdColumn(E10:$AV10,8)

To do the Actual figures in columns 2, 5, 8, etc, of the block, it's

=SumEveryThirdColumn(F10:$AV10,8)

For the variance,

=SumEveryThirdColumn(G10:$AV10,8)

You'll have to work out the formula to determine the number of values to be
included based on the date in A6. You would substitute that calculated
number of months for the 3rd argument, the 8, in the above examples.


Option Explicit

Function SumEveryThirdColumn(Rng As Range, NumToSum As Long) As Variant
Dim C As Long
Dim N As Long
Dim Total As Double
Dim x As Variant

x = Rng.Value
C = 1
N = 0
Total = 0

Do While C <= UBound(x, 2) And N < NumToSum
Total = Total + x(1, C)
C = C + 3
N = N + 1
Loop
SumEveryThirdColumn = Total
End Function


Rafa Zorrilla said:
I need a formula that calculates Year-To-Date figures automatically,
Click to expand...
without editing the existing formula each month. My table is divided in
months (December 2003 to November 2004). Each month has three columns: 1)
budget, 2) Actual figures, and 3)Variation from budget. I thought this was
going to be easy but I've been working in it for a week without good
results. I need a formula that can calculate automatically year to date
figures. It is important to say that budget figures changes every other
month.
The composition of the table is as follows:
1. In cell A6 is the current date [=today()] (MONTH ONLY)
2. Data is on cells e10:av10 (There are 46 rows in the table, but figuring
Click to expand...
out how it works in one row will make the remaining)
3. YTD Results are in cells BA10:BC10 [Budget, Actual, Variation (one column for each result)]
4. 6 months sub totals are in cells x10:z10 (these figures are the sum of the first 6 months)

Since I speak english as a second language, I really hope that you can
Click to expand...
understand what I need. Please let me know if you need any further
information.
----- Rafa Zorrilla wrote: -----

Myrna: I'm trying to apply the formula to a spreadsheet but it is
Click to expand...
not working. Can I email you my spreadsheet so you can take a look at it? If
so, please email me ([email protected]) so I can reply you with the
attachment.
Thanks in advance!
----- Myrna Larson wrote: -----

For last year's YTD:
=SUM(OFFSET($A$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))

For this year's YTD:
=SUM(OFFSET($Y$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))
Click to expand...
 
In your formula
=SumEveryThirdColumn(G10:$AV10,8
this third argument (the 8) is what

----- Myrna Larson wrote: ----

The formula I provided for Sarah has the data for a given month in 1 column
3 rows. You have it side-by-side. So the first formula will definitely no
work

You say the data goes from December through November. Are you dealing with
fiscal year that starts in December rather than a calendar year that start
in January? If so, you can't use the month number directly to determine ho
many cells to include. BTW, if A6 contains the formula =TODAY(), it contain
a complete date, with day, month, and year, even though you have chosen t
display only the month

You say there are 3 columns for each month, and the data is in E10:AV10
That is 44 columns, not 36, so I'm not sure of your layout at all

Anyway, here's a macro that will sum every third column in the first row o
a HORIZONTAL range, beginning with the 1st cell in the range, and includin
the number of values (i.e. the number of months) specified by NumToSum

So if you want to do the budget figures, which are in the 1st, 4th, 7th, et
columns of the block, and you want to do the first 8 months, the formula i

=SumEveryThirdColumn(E10:$AV10,8

To do the Actual figures in columns 2, 5, 8, etc, of the block, it'

=SumEveryThirdColumn(F10:$AV10,8

For the variance

=SumEveryThirdColumn(G10:$AV10,8

You'll have to work out the formula to determine the number of values to b
included based on the date in A6. You would substitute that calculate
number of months for the 3rd argument, the 8, in the above examples


Option Explici

Function SumEveryThirdColumn(Rng As Range, NumToSum As Long) As Varian
Dim C As Lon
Dim N As Lon
Dim Total As Doubl
Dim x As Varian

x = Rng.Valu
C =
N =
Total =

Do While C <= UBound(x, 2) And N < NumToSu
Total = Total + x(1, C
C = C +
N = N +
Loo
SumEveryThirdColumn = Tota
End Functio


Rafa Zorrilla said:
I need a formula that calculates Year-To-Date figures automatically
Click to expand...
without editing the existing formula each month. My table is divided i
months (December 2003 to November 2004). Each month has three columns: 1
budget, 2) Actual figures, and 3)Variation from budget. I thought this wa
going to be easy but I've been working in it for a week without goo
results. I need a formula that can calculate automatically year to dat
figures. It is important to say that budget figures changes every othe
month
The composition of the table is as follows
Click to expand...
1. In cell A6 is the current date [=today()] (MONTH ONLY
2. Data is on cells e10:av10 (There are 46 rows in the table, but figurin
Click to expand...
out how it works in one row will make the remaining
3. YTD Results are in cells BA10:BC10 [Budget, Actual, Variation (on column for each result)
4. 6 months sub totals are in cells x10:z10 (these figures are the sum o the first 6 months
Since I speak english as a second language, I really hope that you ca
Click to expand...
Click to expand...
understand what I need. Please let me know if you need any furthe
informationnot working. Can I email you my spreadsheet so you can take a look at it? I
so, please email me ([email protected]) so I can reply you with th
attachment
Thanks in advance ----- Myrna Larson wrote: ----
For last year's YTD =SUM(OFFSET($A$3,0,0,1,MOD(MONTH($A$1)-7,12)+1)
For this year's YTD: =SUM(OFFSET($Y$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))
Click to expand...
Click to expand...
 
Never mind Myrna, I figured it out
Anyway, do you mean that I cannot calculate the YTD figures if my fiscal year begins in December rather than January?? If your answer is yes, then, do I have to change the third argument of your formula every other month?

----- Rafa Zorrilla wrote: ----

In your formula
=SumEveryThirdColumn(G10:$AV10,8
this third argument (the 8) is what

----- Myrna Larson wrote: ----

The formula I provided for Sarah has the data for a given month in 1 column
3 rows. You have it side-by-side. So the first formula will definitely no
work

You say the data goes from December through November. Are you dealing with
fiscal year that starts in December rather than a calendar year that start
in January? If so, you can't use the month number directly to determine ho
many cells to include. BTW, if A6 contains the formula =TODAY(), it contain
a complete date, with day, month, and year, even though you have chosen t
display only the month

You say there are 3 columns for each month, and the data is in E10:AV10
That is 44 columns, not 36, so I'm not sure of your layout at all

Anyway, here's a macro that will sum every third column in the first row o
a HORIZONTAL range, beginning with the 1st cell in the range, and includin
the number of values (i.e. the number of months) specified by NumToSum

So if you want to do the budget figures, which are in the 1st, 4th, 7th, et
columns of the block, and you want to do the first 8 months, the formula i

=SumEveryThirdColumn(E10:$AV10,8

To do the Actual figures in columns 2, 5, 8, etc, of the block, it'

=SumEveryThirdColumn(F10:$AV10,8

For the variance

=SumEveryThirdColumn(G10:$AV10,8

You'll have to work out the formula to determine the number of values to b
included based on the date in A6. You would substitute that calculate
number of months for the 3rd argument, the 8, in the above examples


Option Explici

Function SumEveryThirdColumn(Rng As Range, NumToSum As Long) As Varian
Dim C As Lon
Dim N As Lon
Dim Total As Doubl
Dim x As Varian

x = Rng.Valu
C =
N =
Total =

Do While C <= UBound(x, 2) And N < NumToSu
Total = Total + x(1, C
C = C +
N = N +
Loo
SumEveryThirdColumn = Tota
End Functio


Rafa Zorrilla said:
I need a formula that calculates Year-To-Date figures automatically
Click to expand...
without editing the existing formula each month. My table is divided i
months (December 2003 to November 2004). Each month has three columns: 1
budget, 2) Actual figures, and 3)Variation from budget. I thought this wa
going to be easy but I've been working in it for a week without goo
results. I need a formula that can calculate automatically year to dat
figures. It is important to say that budget figures changes every othe
month
The composition of the table is as follows
Click to expand...
1. In cell A6 is the current date [=today()] (MONTH ONLY
2. Data is on cells e10:av10 (There are 46 rows in the table, but figurin
Click to expand...
out how it works in one row will make the remaining
3. YTD Results are in cells BA10:BC10 [Budget, Actual, Variation (on column for each result)
4. 6 months sub totals are in cells x10:z10 (these figures are the sum o the first 6 months)
Since I speak english as a second language, I really hope that you can
Click to expand...
Click to expand...
understand what I need. Please let me know if you need any further
information.not working. Can I email you my spreadsheet so you can take a look at it? If
so, please email me ([email protected]) so I can reply you with the
attachment.
Thanks in advance!
Click to expand...
----- Myrna Larson wrote: -----
For last year's YTD: =SUM(OFFSET($A$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))
For this year's YTD: =SUM(OFFSET($Y$3,0,0,1,MOD(MONTH($A$1)-7,12)+1))
Click to expand...
Click to expand...
 
How will the formula change if my fiscal year begins in January instead of December? I really need have a formula that calculate YTD figures AUTOMATICALLY, taking in consideration "today" date.
 
Maybe you should consider a different somewhat simpler layout?
It's not hard at all to get YTD figures using today if the layout of the
tables
are sensible

--

Regards,

Peo Sjoblom


Rafa Zorrilla said:
How will the formula change if my fiscal year begins in January instead of
Click to expand...
December? I really need have a formula that calculate YTD figures
AUTOMATICALLY, taking in consideration "today" date.
 
Not really, I would have to see your workbook first. But if you you would
use a header for each column
that can be used to match a value that you would get from TODAY(). OTOH I
haven't really followed this thread but if Myrna helps you you are in good
hands. She is very clever..
 
The 3rd argument is the number of values to be summed, assuming the first
month of your fiscal year is in columns E, F, and G, regardless of what
(calendar) month your year begins.

I assume the data for the first month of your fiscal year occupies columns
E, F, and G; data for the 2nd month is in H, I, and J, etc.

You will have to work out a formula that calculates the number of months to
be included, based on the month in which the year begins and the current
month, and substitute that formula for the "8" in the example.

A general formula to calculate the number of *completed* months (i.e. do not
include the current month) for a fiscal year that begins in month "X" is

=MOD(MONTH(TODAY())-X,12)

Assuming your fiscal year begins in October, the formula is

=MOD(MONTH(TODAY())-10,12)

If the current date is November 16, the result is 1 (Oct only)

On January 15, the formula will return 3. (Oct, Nov, and Dec). The worksheet
MOD function will return a positive number here: MOD(-9,12) equals +3.
(Integer division of -9/12 gives -1 with a remainder of +3.)

To calculate a full year, you have a "special case". e.g. if the sheet
contains data starting with October 2002, in October 2003, the formula will
return 0. You could use something like


=SumEveryThirdColumn(E10:$AV10,IF(MONTH(TODAY())=10,12,MOD(MONTH(TODAY())-10
,12)))


Rafa Zorrilla said:
How will the formula change if my fiscal year begins in January instead of
Click to expand...
December? I really need have a formula that calculate YTD figures
AUTOMATICALLY, taking in consideration "today" date.
 
Problem solved!!!

Thanks Myrna!!!

----- Myrna Larson wrote: ----

The 3rd argument is the number of values to be summed, assuming the firs
month of your fiscal year is in columns E, F, and G, regardless of wha
(calendar) month your year begins

I assume the data for the first month of your fiscal year occupies column
E, F, and G; data for the 2nd month is in H, I, and J, etc

You will have to work out a formula that calculates the number of months t
be included, based on the month in which the year begins and the curren
month, and substitute that formula for the "8" in the example

A general formula to calculate the number of *completed* months (i.e. do no
include the current month) for a fiscal year that begins in month "X" i

=MOD(MONTH(TODAY())-X,12

Assuming your fiscal year begins in October, the formula i

=MOD(MONTH(TODAY())-10,12

If the current date is November 16, the result is 1 (Oct only

On January 15, the formula will return 3. (Oct, Nov, and Dec). The workshee
MOD function will return a positive number here: MOD(-9,12) equals +3
(Integer division of -9/12 gives -1 with a remainder of +3.

To calculate a full year, you have a "special case". e.g. if the shee
contains data starting with October 2002, in October 2003, the formula wil
return 0. You could use something lik


=SumEveryThirdColumn(E10:$AV10,IF(MONTH(TODAY())=10,12,MOD(MONTH(TODAY())-1
,12))


Rafa Zorrilla said:
How will the formula change if my fiscal year begins in January instead o
Click to expand...
December? I really need have a formula that calculate YTD figure
AUTOMATICALLY, taking in consideration "today" date
 
Just two little differences from your formula:
1. Changed "today()" in your formula with cell A6 (which has also today())
2. Since your formula calculates the month once it is ended, I introduced +1 after the month, this way I can retreive the information from the current month although is has not ended yet.

Once again thanks you and Peo for your help.

=sumeverythirdcolumn(E10:$AN10,IF((MONTH($A$6))+1=12,12,MOD((MONTH($A$6))+1-12,12)))

PS I will visit this forum more often from now on....it has been very helpful for me.
 
Back
Top