Join Dennis Taylor for an in-depth discussion in this video Calculating year-to-date profits, part of Excel 2013 Essential Training.
- View Offline
In this worksheet YTD and Pct Increase, we want to calculate the Year-To-Date Profit. Now, we could do this with Sales or Overhead. We've just chosen to do this with Profits here. We'd like to have a running total month by month of how much profit we've made so far this year. In cell B5, we want to put in the January entry. Let's also be thinking ahead what we want to do in February and March. One thought, as we look ahead here might be, when it comes to February, we will want to add those two cells. When it comes to March, we will want to add these three, in April these four and so on.
That sounds like it might be somewhat complex, but let's approach it this way. What if we figure out how to put in our January Year-To-Date profit, then we can go to February and add these two cells. So a Year-To-Date calculation is relatively straightforward, but maybe a little tricky at first. First of all, let's keep in mind the following idea. Our January profit so far is 20. Maybe these are dollars or thousands of dollars, depends on the size of the operation, but our Year-To-Date profit should always be the same as our January profit.
We're not going to type in a 20 here because what if these numbers change? This number will change. Do we need to write a formula here that subtracts these two? Well, we could do that, but why don't we just say automatically, this cell here is always equal to the profit. In other words, equal B4, no matter what. If we change the Overhead to 95, Enter, these two numbers both change. This is always equal to the one above it. Let me undo that with Ctrl+Z. How about February? Earlier we had suggested we could add these two cells to get our cumulative profit--20 + 30 is 50.
We could also add these two-- 20 + 30 is 50--so what's best? Well, look ahead to March. In March, will we be adding just these two cells to get our Year-To-Date profit? It'll make more sense to take the March entry and add it on to the previous year-to-date, provided this Year-To-Date is the total of these two cells. So let's write a formula here, equal. Now, we can add these two cells in any order we want. It's either going to be C4 plus B5 or the reverse, B5 plus C4.
Remember you can click on cells if you wish-- do it this way--that's an answer. One way to express this formula is: it's the cell above, plus the cell to its left. Will that makes sense over here in March? It surely will. The cell above added to the cell to its left. The formula for February here is the one that we want to copy all the way across. We'll simply drag the corner here into June. A quick check is that our June Year-To -Date profit is the same as the total Profit that we have here.
Writing a simple formula like this ultimately is simple and straightforward, although initially, it might be a little tricky. I'm not saying in any particular way that this formula is needed by everybody, but it certainly is commonly used and it does bring out and emphasize the idea that cell references are really important when you build formulas in Excel.
- What is Excel and what is it used for?
- Using the menus
- Working with dates and times
- Creating simple formulas
- Formatting fonts, row and column sizes, borders, and more
- Inserting shapes, arrows, and other graphics
- Adding and deleting rows and columns
- Hiding data
- Moving, copying, and pasting
- Sorting and filtering data
- Printing your worksheet
- Securing your workbooks
- Tracking changes