Forum Discussion
How would I distribute a value among cells?
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.
I apologize again. New here. I'll edit OP to be clearer after this explanation.
Each day has the following data assigned:
Tasks - Amount of tasks remaining for the rest of the week.
Hours - Amount of hours remaining for the week. Mon-Thurs are 8.5 hours/day. Fri is 6 hrs.
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.
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. Thank you for pointing out the formula error. Not sure how that happened. The EOD number would result from "Tasks" minus "Completed". Fixed and attached the updated file to this post. Will edit it in OP after if possible.
My question has to do with the row of Daily tasks. I'm using ROUNDUP/DOWN right now 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.
Appreciate any help possible. Again, sorry for the confusion.
My suggestion is that you eliminate rounding altogether. Let the number of tasks needed per day be calculated to the first decimal place. You know that 3.8 can be satisfied by doing 3 or 4; and presumably, in the real world, you might actually do 3 tasks completely and only 80% of the fourth. So you'd mark it as 3 completed, but be able to do 4.2 the next day, marking that as 4. And so forth.
Also, from a purely design point of view, it's generally a bad idea to merge cells in the midst of a working area. I've eliminated those from this version. Yes, merged cells can make a layout look better, but save the merging for headers and even then ONLY IF REALLY NECESSARY. You're looking first and foremost for functionality; razzle dazzle can interfere, especially when it comes to copying and moving things around.
You'll see I also made Tuesday and Wednesday more akin in layout to Monday. That's more a matter of personal taste, but there's something to be said for consistency in layout. I would even extend that to Thursday and Friday, but maybe there's a reason why you wanted to cram it all into the corner....
Anyway, see if these changes make sense. As noted, I did away with rounding rather than find a way to make it work; not sure that it was ever really necessary for practical purposes.
- Aw but I love my razzles and my dazzles 😄
I appreciate your help. I'll make some adjustments based on what you suggested.
