Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Percentage Formulas for Spreadsheet

  1. #1

    Percentage Formulas for Spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 worksheetand a table showing the vacation/accrual rates and parameters. Please help!!
    Attached Files Attached Files

  2. #2
    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,
    Attached Files Attached Files

  3. #3
    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!!

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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

  5. #5
    This was cross-posted to Chandoo.org also

  6. #6
    A solution for this was offered at the Chandoo.org also

  7. #7
    Here is the link to the cross-post at Chandoo.org

    (Apparently, I had to have 5 posts before I could post a link!)

  8. #8
    Also cross posted to ExcelForum.com

  9. #9
    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!

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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 ?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •