Help with an Excel spreadsheet to monitor hourly & 12 hourly production figures.

[Falbrav]

Hi,

I have asked this question on another forum, and have been unable to get a satisfactory answer so it's over to you guys:

I am trying to make a spreadsheet which has moving hourly production targets, based upon the previous hours actual production.

For example, If my hours target is 100 (although this number changes based on certain forumlas/criteria) units per hour, but in the first hour of the days (12 hrs) production I only make 50 units and input this into Excel in the actual target production column, I would like Excel to adjust each of the next hourly production targets to 107, due to the 50 units shortfall during the first hour (50 units / 11 = 107.1).


On the hours where I over produce, say for instance, 120 units in an hour, I don't want my target to reduce for the rest of the hours. I would like the remaining hours to still have a 100 target, but only increase, not decrease.

Can anybody offer any advise on how I may get excel to do this?

Sorry if this is a tricky one, it's had me scratching me head for a few days now. I am able to upload the spreadsheet if necessary.

Thanks in advance.

 

[XLXRider]

Need more information... if you require 100 units/hour and make 50 for the first hour, then you require 1150 more units for the next 11 hours.

So this is 1150/11 = 104.55 units/hour for the next 11 hours. 104.55 units x 11 hours = 1150.05 units + the 50 you made in the first hour = 1200.

Note that I could not get 50/11 to equal other than 4.545, so don't know how you're calculating this 107.1 number.

I write production tracking sheets all the time; this shouldn't be too hard to write, given accurate information with which to start.

Regards,
XLXRider