My company requires us to break out our timesheets by percentage according to grants and then report how many hours per grant we worked to the nearest quarter hour, but when doing this and rounding the final hours don't sum to the original total.
I've attached an example.
Any ideas?
Thanks!
Hi adopotato. Welcome to the forum.
I guess I am not getting the concept here.
Are you referring to the % figures in D13:D19 and their sum? Their sum is 100%. If you are not aware of it the formatting of those cells is cosmetic. The underlying values remain as they were before formatting. Those values are used in the calculations E13:K19.
The sums of in E13:K19 in row 20 equal the figures in row 10.
What am I missing?
Dave
Thank you for responding FlameRetired.
I'm sorry for not being more descriptive. I understand how all the formulas are working at this point, but the values in E13:K19 should show 3.25 or 3.5 or 3.75 instead of 3.27, 3.61 etc. I've attempted to use ceiling, floor, round and mround and they all work correctly, but once rounded the subtotal for the column in row 20 doesn't match what the employee reported in row 10. Applying this to Monday of the second week N13:N19 provides a good example. Accounting needs the values in E13:K19 and M13:S19 to be to the nearest quarter hour. Does that explain it better?
Thanks,
Andres
Try to round n-1 rows only, last row is equal row 10 minus total of n-1 rows.
For ex, mround from row 14 only, and row 13 is equal = total - sum row 14 to 19
In F14 then copy to F19
=MROUND(F$10*$D14,0.25)
F13:
=F$10-SUM(F14:F19)
Copy F13:F19 to all other columns.
Quang PT
Quang,
Thank you! That worked just like I needed it to. After running through it, it seems so simple. I really appreciate the help!
Andres
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks