How to Use Excel to Calculate Year to Date Figures

Written by james t wood Google
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use Excel to Calculate Year to Date Figures
Calculating all the data for this year will provide the year to date results. (Jupiterimages/Photos.com/Getty Images)

Your boss wants to know how well your division is doing and you need to give her the answers. Calculating year-to-date numbers is common in business because people want to know how well, or poorly, things are going at a particular moment in time. Figuring the YTD requires using the date calculation features in Microsoft Excel to figure the difference between two dates.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Add up all the data you want to see in a YTD format. Use the "SUM" feature in Excel to add all the cells in a range. For example, if you entered all of your yearly sales in column "B" by month, you would want to sum up cells "B1" through "B12" so you would click in cell "B13" and type: "=SUM(B1:B12)" then press "Enter." This will provide a total for all the sales to this point in the year.

  2. 2

    Create two date fields on your speadsheet. One is the last day of your financial year, for example enter "12/31/2011" the other is the current day. Say that cell "C1" is the last day of the year and "C2" is the current date. Compare the two dates by using date arithmetic in Excel. Find the number of days between the two dates by entering the following formula in a blank cell: "=DAYS360(C2, C1)" then press "Enter." Find the percentage of the year currently gone by entering the formula: "=360-DAYS360(C2, C1)/360" then press "Enter."

  3. 3

    Analyse your YTD number against the date information you generated. For example, to find your YTD percentage to your goal you would need to add a cell for your yearly goal. In this example, put the yearly goal under the YTD sum, so cell "B14" would contain the yearly goal. Enter the following formula in an empty cell to find the raw percentage from YTD to your goal: "=B13/B14" then press "Enter."

  4. 4

    Compare the percentage of YTD to goal with the percentage of the year currently gone in another cell. For example, say your percentage of the year passed is in cell "C3" and your percentage of YTD sales to your goal is in cell "B15" you can enter the following formula to compare the two: "=B15/C3" then press "Enter." This will give you the percentage over or under your goal you are at this point in time, so if you are doing better YTD than your yearly goal you might see, "127%" but if you aren't meeting your YTD goal you might see, "62%" displayed.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.