Moving hourly production target

Hi,

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

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

That is exactly what I wanted. Thank you very much, I tried for hours on that last night.

I didn't think there would be a solution to that. Thank you again!

Sorry, one other question:

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.

Is this possible in excel?

Great, thank you very much for the quick reply. That is excellent.

I very rarely overproduce, so this shouldn't be a problem.

Originally Posted by Ace_XL

Change Column D to

=IF(C2<>"",C2,MAX(100,B2)).

Note: This will lead to over producing as against your original target. See attached

Hi,

Your formulas work very well, but I am having great difficulty in incorporating them into my existing spreadsheet.

Would you be able to offer further assistance, as I am getting myself very confused?

Many thanks

F.

Actually, if anyone else would be able to assist it would be much appreciated.

Hours later I'm no nearer completion, and am now more dumbfounded than when I started.

Have you had a chance to look at this problem for me?

Basically, the problem I am getting with this, is that if I alter =IF(C2<>"",C2,MAX(100,B2)) to =IF(C2<>"",C2,MAX(=F1/12,B2)) then the forumla doesn't work anymore.

In fact, If I alter it to any number other than 100, or input any cell reference it fails to work any more and I need the spreadsheet to be flexible.

I'm guessing, by the lack of responses, that I'm asking excel to do the impossible here, right?