Automatically calculate values

Copper Contributor

brianf00_0-1656085544414.png


Is there a way to make the "WHAT YOU WILL NEED TO RUN" update to equal 6 based off values put into actual tpph?

10 Replies
Is there a way to explain more fully what you mean? I suspect the answer to the question is "Yes," but the question is quite unclear. "Based off values put into .." --what's the relationship between that phrase "based off", the "6" that you desire, and the various "projected" values of TPPH?
for example...
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

@brianf00 

 

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.

@brianf00 If this is what you need.... image.png

 

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. 

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 day
Do you want to 'smooth' the projected need (6.33) or do you want to know the full deficit?
The full deficit approach tells you what you need to be caught up that day. The smooth approach tells you what level you need to maintain for the remaining days.
smooth

@brianf00 

If the number remains the same, there is no need to repeat it. Just put the result in one cell. Hope this helps.

 

CraigHatmaker_0-1656192308859.png

 

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))

@brianf00 

 

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.

mathetes_0-1656202367905.png

 

 

The problem is there or not you need to check, as well as an alternative to the reliability of the communication site, perhaps a duplicate, as a rule, such problems are disagreements of the parties.
Tag Urgent,Disconnection,Technical Issue,Security