Balance Amount Continue

[sambit]

Sir,

Please help to resolve the issue.

Balance amount should be continue after put some amount in subsequent cell.

Example file attached for your reference.

 

[Luke M]

Formula in H6 should be

=H5+G6

and then copy that down.

 

[sambit]

Luke M Sir,
The above formula is not working.

 

[vletm]

sambit​

to cell H6 =IF(G6<>0,$E$2+G6,H5) and copy down

 

[Hans Knudsen]

I would propose formulas like those shown in column P.

 

[sambit]

sambit​

to cell H6 =IF(G6<>0,$E$2+G6,H5) and copy down

vletm sir,
Thank you for your response. it is working fine on column H. but as per my requirement we can not change anything in column H. What ever the changes in column I. My request you to please review again.

 

[sambit]

I would propose formulas like those shown in column P.

Hans Sir,
Thank you very much. working fine.

 

[vletm]

sambit​

...hmm?
Your but as per my requirement we can not change anything in column H
Where is above requirement?
Where should Your Output be?
... then it (Balance amount) cannot be in H-column ... ?
Your sample's
# I-column ... is empty.
# Your text's Required Output Look Like Below shows to K-column.
# and finally K-column looks different than below Req output- text (L-column)

I could ask same My request you to please review again.

 

[p45cal]

In the attached, see column Q where there's a lambda function:


where you can see there are hints as to what belongs where:
rws is the number of rows you want in the result,
start is the value 2000 in cell E2,
toAdd is the range of values to add which must be a range encompassing all the values you want to add, it doesn't have to be 6 rows deep, it's better if there are no blanks between values.
In column P (not needed) is the longhand version of the lambda formula:

=LAMBDA(rws,start,toAdd,LET(ta,FILTER(toAdd,toAdd<>"",{0}),EXPAND(start,rws,,start)+EXPAND(ta,rws,,MAX(TAKE(ta,-1)))))(6,$E$2,J33:J38)

I was just playing with this, I'm sure it could be more elegant, and maybe tweaked to allow non-contiguous values in column J.

 

[p45cal]

and maybe tweaked to allow non-contiguous values in column J

OK, did this, see attached column P:


Only 2 arguments as the number of output rows is determined by the number of rows in the toAdd argument.
For info only, lambda formula in cell Q5.

=LAMBDA(start,toAdd,LET(rws,ROWS(toAdd),ta,SCAN(0,toAdd,LAMBDA(a,b,IF(ISBLANK(b),a,b))),SEQUENCE(rws,,start,0)+ta))($E$2,J5:J10)

actually, could have been simpler:

=LAMBDA(start,toAdd,SCAN(0,toAdd,LAMBDA(a,b,IF(ISBLANK(b),a,b)))+SEQUENCE(ROWS(toAdd),,start,0))($E$2,J33:J38)