Forum Discussion
Automatically calculate values
projected tpph is 6.25 for Friday
Say TPPH for Wednesday is 5.5 and Thursday it's 5.1
then the "WHAT YOU WILL NEED TO.." boxs will update with outcomes needed on the days following to equal 6
I'm sure that's clear in your own mind. It's not there yet in mine.
WHAT is the relationship between the top "Projected" set of values and what you're trying to do? I can see no way, for example, that the 6 at the end of the week is the outcome of that string of values.
And your example mentions a projected value of 6.25 for Friday, actuals for Wed and Thu of 5.5 and 5.1, but the week runs Wed-Tue, so there are still five more days, each of them with their own "projected" values (as noted above, their relationship with the whole thing is murky). Is the desired 6 at the end of it all an average....
Can you walk through a manual example of how you calculate this now, in the absence of a nice tight Excel formula. All the way through to the final 6, not just the first two days.
As I said at the start, I suspect that the formula you are seeking will be relatively easy, but you haven't yet explained the underlying relationships here that need to be turned into a formula.
What, by the way, is TPPH in the first place? I realize that it doesn't really matter in the math, but it's part of "the black box" that you've given us.
- so...
All the projected values added together then the sum divided by 7 equals roughly 6...
The actual is what is ran each day. So 6.25 times the 6 days + the 5 Sunday is 42.5
42.5/7 equals the 6 for the week.
You run a 4.5 for Wednesday actually then you're left at 38 for the entire week
So 38/6 remaining days would be 6.33 each dayYou wrote: All the projected values added together then the sum divided by 7 equals roughly 6...
Which, quite frankly, I find more than a little bit frustrating. Up until now, there's been no indication that you wanted anything other than precision. It makes me wonder how much margin there is in this, why you're bothering asking for a formula. You could just produce 38 in the second day and be done with it (again, you've not really given a picture of what is actually going on here; so I realize that is most likely not a useful suggestion, but there's been no indication of why not. Why not just do whatever is needed to get a weekly total of 42 and be done with it?) So, it's possible that there remain some unspoken but important constraints here--things you're taking for granted: for example, is there a minimum number of TPPH (whatever that is) that has to be achieved each day; is there a maximum, a number beyond which you can't go? You've not specified any such constraints, so the question of "Why not just produce 42 for the week and be done--that would yield your 6 when divided by seven--why need to produce something every day?"
That said, if you don't mind having a number produced that actually delivers 6.0000 as the end result, this formula put in to the cells beginning with B5 and then copied to C5 through to G5 will yield your 6.0
=(42-SUM($A$5:A5))/B4
assuming that you overwrite cells B5, and then C5, and then each ensuing number at the end of each day's ACTUAL run with the actual produced, just being sure on the last day to be exact with what is needed.