# YTD formula for paycheck stub

Discussion in 'Business Applications' started by ems3536, Aug 27, 2012.

Not open for further replies.

Joined:
Aug 27, 2012
Messages:
11
I have been trying to figure out a way to implement a formula for a YTD total for these columns...can someone help me please??

File size:
11.1 KB
Views:
125

3. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
can you explain a bit more detail on whats required - i seem to be missing the requirement

4. ### valisModerator

Joined:
Sep 24, 2004
Messages:
72,630
my thoughts exactly........which YTD totals are you looking for? You should just be able to enter a sum formula and select what cells you wish to add.

Joined:
Aug 27, 2012
Messages:
11
I have tried that with the attached spreadsheet and it gives me an error message. I would like a YTD formula in each column next to the deductions and the earnings. It needs to be a formula that adds the current YTD column with the new earnings that are entered as well as the new deductions. And keep a running total. Is there a way for me to do that? I am entering manually as of now.

6. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
sorry i'm still missing the calculation, and sure its something simple i'm not understanding

in E11 you have 4031 - how is this worked out from the rest of the sheet

Joined:
Aug 27, 2012
Messages:
11
That 4031 is where I entered it manually. This is based on a weekly payroll that I am calculating and trying to create a paystub. So, every week, I manually take what is in the YTD column (E11) and add the E10 to it....make sense?? I also do that to every other YTD column except for the one at the bottom of the sheet.

8. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
no - really sorry , i just dont understand what you are doing
I understand the year to date concept and it can be done with a few formulas

but E10 - is the title "year to date" - so I dont know how you

so the year to date numbers
\$4,031.00
\$1,672.50
\$116.00

i would have a new column somewhere which keeps last week/month "year to date" and then when you add the current figures it can add in last months to get a new YTD
but that would involve copying the new YTD and current to text

or keep a running reference for each paydate and update

quite a few ways to do what you want - just dont know exactly what you need

how many are you doing - just the one sheet?

Joined:
Aug 27, 2012
Messages:
11
I meant D10...sorry....I just need some basic formulas to calculate a YTD without me having to do it manually for E11-14 and J7-10 without me having to do it manually every week. I need to be able to add the current calculation of each D11-14 and each I7-10 to their respective YTD totals. If that means I have to enter it in somewhere else, and then copy---I have no idea how to do that...any help to get a decent paystub working correctly so that all I have to do is enter the hours worked and everything else works like it should, I would be appreciative.

10. ### Pedro15

Joined:
Oct 5, 2008
Messages:
385
A couple of questions.
E11= \$4031 Less D11 \$290 gives \$3741 which is of course the YTD prior to the entry in D11.

Where does this figure of \$3741 come from and what about dates.

Same for entries in column J.

Pedro

Joined:
Aug 27, 2012
Messages:
11
I have just been manually entering the total for the YTD in each D11 each week. Should I keep one spreadsheet with 52 tabs for each week?? I am unsure of what you mean about dates....and the same goes for the entries in column J. I manually add them together with the new entries.

I hope this helps

12. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
yes - or on the same sheet with a list of dates with the salary figure in
then we could look up the amount each week
based on the date

Joined:
Aug 27, 2012
Messages:
11
So...could I just start from this last one dated Aug 25 and begin the tabs on this sheet and continue from there??? What should I do then? I used to think that I was Excel savvy....until now.....

Joined:
Aug 27, 2012
Messages:
11
Any chance you can open the document and alter it???

15. ### etaf Wayne Moderator

Joined:
Oct 2, 2003
Messages:
63,180
Ok - so this how i have setup , may not be exactly what you want to do

In column N and O
I have added the dates of the weeks and next to that I have made up some figures for the salary for that week

I have only done the Row 11 calculation
So in E11, i look at the date in the cell A22 - I then take away 7days to get last weeks date
Now I use vlookup to goto the column N and look for the date in that column - once found , i then look at the same ROW in column O to get last weeks salary
I then add the salary from cell D11 to calculate this weeks salary

Now before you save this document , you need to copy this weeks salary and paste special > value into the column O next to this weeks date
so for this week that would be cell O12 would need to have the value 4031 copied- BUT it must be copied as a value and not the formula
now save the sheet

next week comes
and you change the week date in cell A22 -straight away
in Cell E11 it will find last weeks entry of 4031 and add D11 to it

does that sort of work for you
if so - then all the other cells should be able to be setup to do the same ?

see attached example

#### Attached Files:

• ###### paycheck stub_etaf.xlsx
File size:
12.1 KB
Views:
67

Joined:
Aug 27, 2012
Messages:
11
Yes! That works! I would have no idea how to do that formula!! I guess, I can figure it out for the other columns....thanks for your help. For the other formulas, do I just create other columns next to the date and just change the formula to like p,q, r, etc??? But I could put headers at the top or something like that??

As Seen On