Forum Discussion
How would I distribute a value among cells?
Hello all. This post has been edited for clarity. Please let me know if I need to elaborate further. Thank you.
I have a chart I created in order to manage how many tasks I must complete each day in order to finish them all by end of the week. Since 1 of the 5 days I work has less hours and sometimes I cannot complete the number of tasks needed on a day, which will affect the rest of my week.
Each day has the following data assigned:
Tasks - Amount of tasks remaining for the rest of the week. All days but Monday, this is taken from the EOD from the prior day.
Hours - Amount of hours remaining for the week. Mon-Thurs are 8.5 hours/day. Fri is 6 hrs. Starting at 40, each day subtracts 8.5 from the prior day.
Daily - The amount of tasks to complete that day in order to stay on pace to complete all by weeks end. This is calculated by the number of hours that day divided by the Hours remaining for the week, then multiply the resulting percentage by the number of Tasks at the start of that day (Example: Monday's Mon formula =8.5/F3*D3, where F3 = 40 and D3 is Tasks).
Completed - I change this manually as I complete the tasks to calculate how it will affect the rest of my week if I fall short or go over those hours.
EOD (End of Day) - Amount of weekly tasks remaining. EOD number results from "Tasks" minus "Completed".
My question has to do with the row of Daily tasks. I'm using ROUNDUP/DOWN right now in select days in an attempt to get the "Total" at the end of the week as close to accurate as possible. "Total" is simply there as a check because I don't know how to distribute the tasks along those days evenly based on the hours available each day. That was what I was hoping to rectify here with some guidance. How can I make it so that I'm getting the proper percentage of hours applied to the total task for the day but still have all 5 days equal that total?
Appreciate any help possible. Let me know if this can/should be explained differently. Will edit accordingly.
I may be missing something entirely obvious, but I don't "see" what you claim I should be able to see. It looks to me as if the daily totals are indeed the totals of the numbers to their left. There is clearly some relationship between numbers that YOU think is obvious, but isn't. So unless someone else comes along who gets what I've failed to get, perhaps you could give a more complete explanation.
Ideally, if you have the ability to post the original to OneDrive or GoogleDrive, with a link pasted here that grants access, doing that would enable us to help you far more readily than an image.
I apologize. I didn't know that was something that was allowed. I was part of another site that wouldn't allow for it.
If you look at the image, it shows that the End of Day number for Monday is 12. However, when the appropriate hours are used towards calculating how much of that 12 should be spread over the remaining work days, it totals to 14.
Here is the Drive link:
If you look at the image, it shows that the End of Day number for Monday is 12.
Yes, it does. But, and this is just one of the things where this spreadsheet does NOT make sense, that number 12 in cell E6 is drawn from Tuesday's D8. That is to say, it's taken from the future!!!!!
However, when the appropriate hours are used towards calculating how much of that 12 should be spread over the remaining work days, it totals to 14.
And where, pray tell, are those "appropriate hours"? Are they the hours shown in Tuesday? (those Tuesday figures are the ones that add up to 14) Which, if that's the case, would again be a "how does this make sense?" instance. How, more importantly, WHY could Tuesday's hours add up to a Monday figure of any kind?
So you still haven't made this whole design make sense to me. If it makes sense to you, please explain what each day's box contains, how each day's numbers make sense for that day, how they connect to other days in the week. In short, how is this supposed to work? What is it really about?
I'm attaching an Excel version of the sheet here, in case some of the other regulars in the forum care to take a look.
