Results 1 to 7 of 7
Thread: CALCULATE YTD (EXCEL 97)

20010727, 07:50 #1
 Join Date
 Apr 2001
 Location
 SOUTH AFRICA
 Posts
 74
 Thanks
 0
 Thanked 0 Times in 0 Posts
CALCULATE YTD (EXCEL 97)
Hi All,
Can anyone please help me with the formula if I want to calculate the current year's YTD & previous year's YTD, always using the latest month. Please see example attached.
Thanks a lot

20010727, 09:38 #2Guy HaversGuest
Re: CALCULATE YTD (EXCEL 97)
Hi Rene
If I understand your question right, it is possible to achieve a moving range using OFFSET. On your sheet the formula in B2 would read:
=SUM(OFFSET(O2,0,0,1,TEXT(NOW(),"m")))
where O2 is the anchor, the first two zeros are the offsets (we don't want any), the 1 is the number of rows and the last bit is a formula to calculate the number of columns to include, which crawls slowly to the left as the year progresses.
Hope this is what you meant
Guy

20010727, 10:08 #3
 Join Date
 Apr 2001
 Location
 SOUTH AFRICA
 Posts
 74
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: CALCULATE YTD (EXCEL 97)
Hi Guy,
Thanks for your reply. I actually adjusted my attached worksheet as I realised this it is not going to be so straightforward. I will always have 24 months, as my example now shows, I am in month July2001. The worksheet contains months July2001  Aug1999 (24 months) Now what I need is current year's YTD, which will be the sum of Jan2001Jul2001. The previous year's YTD should be Jan2000Jul2000
The way I see this is that I cannot have a specific cell as an offset. I don't know how to approach this issue.
Thanks again

20010727, 10:53 #4Guy HaversGuest
Re: CALCULATE YTD (EXCEL 97)
Hi Rene
Don't quite know how you update the sheet each month, but I attach a couple of ideas to get round the problem.
The new improved formulas on rows 5 and 6 are automatic, but will give wrong answere if you are (say) working a month late. A second approach is to input the month number in a cell (b2 in this case). The formulas in rows 8 and 9 are looking at it and will adjust the offset as necessary.
Hope this helps
Guy

20010727, 12:08 #5
 Join Date
 Apr 2001
 Location
 SOUTH AFRICA
 Posts
 74
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: CALCULATE YTD (EXCEL 97)
Hi Guy,
The formula using the month number is the perfect solution. If you have time, I will appreciate it if you can explain the formuli,, I can figure out most of it, but not all.
Thank you very much.

20010727, 13:50 #6Guy HaversGuest
Re: CALCULATE YTD (EXCEL 97)
Hi Rene
I'll try... Example formula:
=SUM(OFFSET(C8,0,$B$2,1,$B$2))
OFFSET is defining a range for SUM to add up. Offset has 5 switches, separated by commas.
The first switch is the location of the 'anchor', which I have set at column C and the current row (8).
The second and third switches define the 'starting point' cell of the range by expressing how many rows and columns away from the anchor it is. In this case Rows is left at zero ( meaning same row) and the number of columns is read in from B2. If B2 holds the right number, the starting point should always be in the January column.
The last two switches define the size of the range block in rows and columns. 1 refers to rows (we only want one), and the Negative B2 will select the columns all the way back to column D. (Positive B2 would select columns to the right)
The prior year formulae simply add 12 to the month number to take the starting point over to the previous January.
Hope this makes sense. Theres a good bit in Exel help as well.
Guy
PS =SUM(OFFSET(C8,0,1,1,$B$2)) & =SUM(OFFSET(C8,0,13,1,$B$2)) work just as well, and are simpler  I just didn't think of it earlier!

20010730, 06:51 #7
 Join Date
 Apr 2001
 Location
 SOUTH AFRICA
 Posts
 74
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: CALCULATE YTD (EXCEL 97)
Hi Guy,
Thanks a lot, you explained the formula like a Pro. (This is the way we call Professionals in South Africa).
Enjoy your day.