Percentage Formulas for Spreadsheet

nishi23

New member
Joined
Oct 10, 2013
Messages
4
Reaction score
0
Points
0
I need help modifying an existing spreadsheet. Our employees accrue time based on percentages, and this spreadsheet would be great to represent their accruals for time tracking and vacations. This workbook was created by an unknown person and is based on hours, but we need help making this work for our percentage based system. We want to track our employees' sick time, but they do not get penalized for them. We like the technology that this excel workbook offers to track their vacation accruals, but we are having trouble converting it for percentage representation. Attached is a sample worksheet[FONT=inherit !important][FONT=inherit !important] [/FONT][/FONT]and a table showing the vacation/accrual rates and parameters. Please help!!
 

Attachments

  • Percent Calculation for Accrued Time.xlsx
    11.9 KB · Views: 29
  • NEW FY2013-14 Template.xls
    82.5 KB · Views: 20
Good afternoon,

I changed a few formulas (highlighted). If I have misinterpreted your data please let me know where I'm off.

Best of luck,
 

Attachments

  • Vacation_Accrual_sample.xls
    94.5 KB · Views: 34
Thank you so much this is exactly what we want! You are amazing!

Can we copy and paste these formulas for the other percentages or what exactly would we have to change on for each differing percentage group? As of now, the worksheet works exactly how we want it to for employees who work 100%. So hopefully we can make it work for employees who are 80%, 60%, etc. So can we apply this for the other percentages?

Thank you for your help!! :)
 
Hello
You should be able to work towards just changing the percentage in I16, but you will need to remove the duplicated use of the percentage in your Vacation and Sick tables. Whilst your working with 100% (i.e 1), it makes no difference

1.7*100%*100% still = 1.7, but 1.7*80% = 1.36 in E10 (correct).... but then E10*80% in the vacation formula gives 1.088 (wrong?). Once resolved, I would create 7 copies of the corrected first sheet and edit the % in I16 and the name of each sheet.

HTH

Hercules
 
This was cross-posted to Chandoo.org also
 
A solution for this was offered at the Chandoo.org also
 
Here is the link to the cross-post at Chandoo.org

(Apparently, I had to have 5 posts before I could post a link!)
 
Thank you so much for making our spreadsheet work for employees that work 100%, that is exactly what we want!. However, we were trying to make it work for the other percentages and we couldn't get them to work. We tried changing the formulas to work for 80%, 60% and so on but we could not get them to function like they did in your formulas for 100%. I appreciate everything that you have done so far but if you could help us with the formulas for the other percentages that would be awesome!
 
nishi23
If you look at post #4 I pointed out some snags that would prevent other percentages working correctly. Did you put these right before trying a different percentage ?
Also can you post your sheet thats trying to work with a % other than 100 ?
 
Errors for different percentages

Hercules1946
Hey I saw your post #4 but I had difficulty exactly understanding what you meant, so I am not sure if I addressed these issues before trying it for a different percentage. Attached is the sheet with multiple tabs representing the different percentages, but as of now only the one for 100% works. I tried making it work for 80%, however our formulas are not working correctly. As you mentioned in post #4 I keep on getting the incorrect value of 1.088 but I am not completely clear on how to fix that.
 

Attachments

  • Vacation_Accrual_sample (4).xls
    96 KB · Views: 11
nishi23
No - your holiday formulae were still applying the % ratio twice, therefore taking off too much from the gross allowance of 1.7, with any % apart from 100. Ive corrected this on the attachment, which has percentages from 100 down to 20 in steps of 20. You can now generate more by doing a copy of any of them and changing the % in I16.
I haven't tried to change the sick figures, because based on your formula its always going to be 1 day per month anyway.


HTH

Hercules
 

Attachments

  • Vacation_Accrual_sample (4).xls
    183.5 KB · Views: 12
Back
Top