Results 1 to 7 of 7
  1. #1
    Star Lounger
    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
    Attached Files Attached Files

  2. #2
    Guy Havers
    Guest

    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

  3. #3
    Star Lounger
    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 Jan2001-Jul2001. The previous year's YTD should be Jan2000-Jul2000

    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
    Attached Files Attached Files

  4. #4
    Guy Havers
    Guest

    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

  5. #5
    Star Lounger
    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.

  6. #6
    Guy Havers
    Guest

    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!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •