Calc dynamic running total in horizontal sequence

[Giovi]

All,

I know the title may be a little confusing so hopefully I can clarify. I have forecast values in horizontal layout and all fractional values. I would like to add each cell till their sum = 1, and then have the calculation start all over again (accounting for any remainder >1). Is this possible without VBA?

I have attached a workbook with a row entitled "ideal output".

Thanks again for taking the time and consideration to look at this!

[Crooza]

Put "1" in B6 and

=IF(SUM($B$2:B2)>SUM($B$6:b6),1,0) in c6 and drag across

[FDibbins]

Just to clarify, you are adding quarters, and want the sum to show only in the 1st mth of the qtr?
And you want to sum to a max of 1, and the remainder carried over to teh start of the next qtr?

[Giovi]

FDibbins,

It just so happen in this example it comes out to every quarter. But if you look at cell T2 and U2 you will see what i am trying to accomplish. The example i gave is just one material. i have a list of over 1000+. There are some materials that will need go out 7 months and ideally i would like "1" to fall in M1 and then the start of the next sequence in M8 if that makes sense.

I have updated the worksheet to give you an idea of the demand patterns for one part across all plants.

As you can see in row 15, the first cell starts with 4.38. The idea is to distribute the remainder and rounding each value to a whole number.

Just to clarify, you are adding quarters, and want the sum to show only in the 1st mth of the qtr?
And you want to sum to a max of 1, and the remainder carried over to teh start of the next qtr?

[kvsrinivasamurthy]

Try this.
In B8 then copied across.

=IF((SUM($B$2:B$2)-SUM($A$8:A$8))>=1,1,0)

[Crooza]

Just to clarify, you are adding quarters, and want the sum to show only in the 1st mth of the qtr?
And you want to sum to a max of 1, and the remainder carried over to teh start of the next qtr?

They're not all quarters and my formula seems to account for that.

[Giovi]

KVSR,

It isn't the desired pattern. Ideally, everything is pushed to the most left in this example. I have updated the worksheet, how would this work with values that are > 1 (example row 15).

Try this.
In B8 then copied across.

=IF((SUM($B$2:B$2)-SUM($A$8:A$8))>=1,1,0)

[Giovi]

Crooza,

The pattern is ideal, but it doesnt work for values where the cell is >1 already. For example row 15 or row 17.

They're not all quarters and my formula seems to account for that.

[Crooza]

KVSR,

It isn't the desired pattern. Ideally, everything is pushed to the most left in this example. I have updated the worksheet, how would this work with values that are > 1 (example row 15).

I assumed that you have the discretion to set the number. I really don't know what you'
re doing only that the formula I supplied in post #2 matched your desired output. I'm looking at this as putting $1 in the bank and when it gets drawn down putting another $1 in the bank. What do you want the number to be in row 15? How would you go about determining it manually?

[kvsrinivasamurthy]

Here is the revised formula.

In B8 then copied across

=IF(SUM($A$8:A$8)+SUM($A$2:A$2)=0,1,IF((SUM($A$2:A$2)-SUM($A$8:A$8)+1)>=1,1,0))