Above data, from the first row accumulation, when data is more than 56, switch to the second column, the result, each column value accumulation is 56

CodePudding user response:

Personal opinion: need to insert a hidden among each column column



The first column from the second line formula, need nested if outlining
The first layer is whether the original data column 1 - n the accumulation is greater than or equal to 56
Less than 56, the output of the original data
Greater than or equal to 56, the need for judgment, because of the need to accumulate a value of 56, so leave a value of [56 - Sum (1 - n)]
But the situation is only applicable to the last one you need
If applied to other lattice, can output negative
So add a second if judgment

 
=the IF (SUM ($3 A: A4) & gt;=56, the IF ((56 - SUM ($3 A: A3)) & gt; 0, (56 - SUM ($3 A: A3), 0), A4) 

Hidden columns YC1

 
=the IF (SUM ($3 B: B6) & lt;> 56, 0, (A6 - B6)) 

CodePudding user response:

IF (COLUMN ()=2, the IF (SUM ($1 B: B2) + $A3 & gt; 56, 56 - SUM ($1 B: B2), $A3), IF (SUM ($1 B: B2) + $A3 & gt; 56, 56 - SUM ($1 B: B2), $A3 * 2 - SUM ($A3: A3)))

CodePudding user response:

Give a way of thinking, in fact, the key is to find out the need to change the column of the line, is equal to the total and divisible 56 of the value of the line to the value of that column, count from the bank began to fill, fill in and move on,