Help with calculating YTD in Excel

Im trying to create a formula in excel for paying my employees, where i punch in one number (amount of hours) and it spits out their paycheck total.

I got everything set up and working like a charm except for the YTD column.

I have D2 as the gross earnings amount, and i wanted E2 to be the YTD amount for gross earnings.

I would think if i formulated E2 as “e2+d2” it should work, but it brings up an error message as being cyclical

Same problem being with the YTD net pay.

I tried searching google, but i couldn’t really find anyone looking to do what i am.

You may have better results with a payroll system, rather than building your own spreadsheet. I know just enough about payroll to know it gets complicated very quickly, and the time and effort you save will be your own. If you aren’t familiar enough with Excel to solve this problem quickly, then you’re going to have tough sledding when it comes to taxes and deductions and so forth.

But if you just want an Excel answer, I can help.

If column D is the weekly gross earnings, you want column E to be the Year-To-Date total gross earnings, is that right?

If that’s what you want, then the formula you want in cell e2 is “=sum(d\$2:d2)”. The Excel sum function adds up the contents of as many cells as you reference. With that formula the value of e2 will be the sum of all cells from d2 to d2, which is a roundabout way of saying e2 will equal d2. Why use the weird formula? Because when you copy cell e2 and paste it into cell e3 you’ll get a slightly different formula in e3: “=sum(d\$2:d3)”. That’s the sum of cells d2 (week 1 gross) and d3 (week 2 gross). Keep copying/pasting the e2 formula into the column e cells for all 52 weeks. You’ll end with a formula like “=sum(d\$2:d53)” in cell e53. So you are on you way.

The trick is the dollar sign. Here’s a web page that explains it. I’ve found the mrexcel site to be very helpful!

BTW, the circular reference issue is because you were making a formula where the value in cell e3 was that value plus something else. Suppose d2 was 5 and e2 was 3. The e2=d2+e2 becomes 5+3 = 8. But now e2 is 8, and 8+5 = 12. But now e2 is 12, and 12+5 = 17. This will not stop! Excel is smart enough to recognize the problem, and prevent you from causing it.

Where are you storing the values for the previous weeks? If week 40 is in D2 then each of the proceeding weeks needs to be stored some place to be able to add together.
I’m not sure but I get the impression you think Excel will just remember what E2 used to be and then add the value of D2 to it.
It definitely won’t do that - at least not like that.

Absolutely correct - you cannot set a cell to equal itself plus something else. It’s the very definition of a cyclical reference.

You can do that sort of thing as a discrete operation in most programming languages - and perhaps this is where the confusion is arising.