Using IF statement to carry over Grand Totals based on Base Number

[ortlepp]

I need some help with a Formula to figure out the following.

I have a number of Grand Totals that equal to Hours of Work in a day ( Based on Demand from Customer Orders)

I only have 95 ( this will be a number in a cell that I want to be able to change if needed) work hours available to me each working day.

I want each day to attempt to fill in up to 95 hours , anything more and it will push the remaining balance forward into the other cells.

IE here is what I have for the next 5 days for Totals

211.3798 120.3048 70.0099 110.7343 75.0418 28.1292

Under the 211 I want it to change to 95 and then carry over the balance to the cell under 120 , I then want that cell to change to 95 and carry over its balance to the next cell and so on down the line. I will always have 22 Working Days I want to work with. So the last day may or may not have a greater then 95 total.

The 95 part I want to be able to change that to whatever number I think I will have available to me and it will adjust accordingly through the line.

I hope that makes sense.

[zbor]

As I see it you need to get:

95 95 95 95 95 95 95 95 95 95 XY

Where XY is what remains from other days and it's added to the last (22nd) day?

Then you don't need to add from each day to next day, you just need to calculate that last day and formula is: =SUM(B2:W2)-(COUNT(B2:W2)-1)*B4

[ortlepp]

Maybe I am explaining it wrong.

95 is what I have available to me each day in work hours. So I can never have more then 95 hours in one day. I need to calculate based on that theory the days I will have Capacity.

So using the example I had , it looks like around day 6 is the first day I will have less then 95 hours needed.

Does that help make more sense of my question ?

[zbor]

can you write how output result should look like?

[DonkeyOte]

If I've understood...

Say A6 holds your hours requirement - ie 95

B4: =MIN($A$6,SUM($A2:B2)-SUM($A4:A4))
copy across to W4

[zbor]

How did you get 2 dimenzions SUM($A2:B2) if everything is in one (B2:W2 in example)?

[DonkeyOte]

I don't follow zbor... my understanding is that the balance in row 4 should be the sum of hours required in row 2 up to and including current column less sum of hours worked up to and including the prior column ... with the MIN value of MAX permissable hours (95) and hours required being the correct result.

To keep formulas consistent (B through W) it is imperative that B4 does not refer to itself... so by setting the ranges to start in A you avoid circular referencing ... given A does not hold numbers it has no bearing on the result.

[ortlepp]

Take a look in Tab 6 , I was getting help from someone else on this and I think its pretty much there.

And Tab 6 was the one that looked good , basically I am pulling data from our planning engine in the form of hours. I am then going to set up something using what you have helped me with and it will allow our Scheduler to attempt to see where she has capicity. On the days that look lower ( further out in the 22 days) she can then attempt to pull in some of those hours to make sure we are always using up the 95 hours each day.

[DonkeyOte]

Maybe I'm missing something but...

given you don't have a spare column before the data starts...

A3: =MIN(A1,A2)

B3: =MIN(SUM($A1:B1)-SUM($A3:A3),$A$2)
copy across to V3

you don't need row 4 at all.

Incidentally the first day you have spare capacity can be determined with: =MATCH(TRUE,INDEX($A3:$V3<$A$2,0),0)
(returns Column number of day - ie using your file and sheet 6 this is day 9 (column I))

[ortlepp]

I can paste the data in anywhere I need to. It will be pulled from another source and pasted into whatever format I need.

[ortlepp]

Maybe I'm missing something but...

given you don't have a spare column before the data starts...

A3: =MIN(A1,A2)

B3: =MIN(SUM($A1:B1)-SUM($A3:A3),$A$2)
copy across to V3

you don't need row 4 at all.

Incidentally the first day you have spare capacity can be determined with: =MATCH(TRUE,INDEX($A3:$V3<$A$2,0),0)
(returns Column number of day - ie using your file and sheet 6 this is day 9 (column I))

Thanks man , this did the trick and worked like a charm.