Jun 24 2022 08:49 AM - edited Jun 24 2022 08:50 AM
Is there a way to make the "WHAT YOU WILL NEED TO RUN" update to equal 6 based off values put into actual tpph?
Jun 24 2022 12:25 PM
Jun 24 2022 02:41 PM
Jun 25 2022 06:44 AM
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.
Jun 25 2022 08:29 AM - edited Jun 25 2022 08:32 AM
@brianf00 If this is what you need....
The formula in C4 is =IF(B3=0,C2,B2-B3+C2)
Copy it to D4:H4
So I assume this is a manufacturing schedule. Projected is what was planned. Actual was what was made. If we were projected to make 6.25 on Wed, but we only made 6, we will need to make 6.5 on Thu. to catch up.
Jun 25 2022 10:17 AM
Jun 25 2022 11:17 AM
Jun 25 2022 02:26 PM - edited Jun 25 2022 02:27 PM
If the number remains the same, there is no need to repeat it. Just put the result in one cell. Hope this helps.
Current Day:=COUNT(B4:H4)
Need/Day: =IF($B$1=7,0,(SUM($B$3:$H$3)-SUM($B$4:$H$4))/(7-$B$1))
Jun 25 2022 05:14 PM - edited Jun 25 2022 05:34 PM
You 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.
Jun 25 2022 06:52 PM