subtract from multiple cells until reach 0

[fastballfreddy]

I'm currently using an if function but want to see if there is an easier formula. I have 7 cells (1-7 columns) and want determine after subtracting Payment (starting from 7 and going to 1) what # how much is left.

For example the first one has payment of 75. Starting from 7 and working down to 1 subtracting would leave 30 left in #6

Below are the 2 formulas I'm currently using which works but reaching out to see if there is a simpler formula that can be use for situations like this. Thanks in advance!

formula for amount left: =ABS(IF(H8-G8<0,H8-G8,IF(H8-SUM(F8:G8)<0,H8-SUM(F8:G8),IF(H8-SUM(E8:G8)<0,H8-SUM(E8:G8),IF(H8-SUM(D8:G8)<0,H8-SUM(D8:G8),IF(H8-SUM(C8:G8)<0,H8-SUM(C8:G8),IF(H8-SUM(B8:G8)<0,H8-SUM(B8:G8),IF(H8-SUM(A8:G8)<0,H8-SUM(A8:G8)))))))))

formula for #: =IF(H8-G8<0,7,IF(H8-SUM(F8:G8)<0,6,IF(H8-SUM(E8:G8)<0,5,IF(H8-SUM(D8:G8)<0,4,IF(H8-SUM(C8:G8)<0,3,IF(H8-SUM(B8:G8)<0,2,IF(H8-SUM(A8:G8)<0,1)))))))

​

1​	2​	3​	4​	5​	6​	7​	Payment​	Amount Left​	#​
100​	100​	90​	80​	80​	80​	25​	75​	30​	6​
100​	100​	100​	100​	25​	0​	0​	325​	100​	1​
75​	75​	75​	75​	75​	50​	35​	250​	60​	3​
100​	100​	100​	100​	90​	80​	45​	100​	25​	6​

 

[Dermot]

Formula for balance in top row, if your table starts in A1
= SUM(OFFSET[H2,0,J2-8,1,8-H2))-H2
copy down

 

[Peter_SSs]

to see if there is a simpler formula that can be use for situations like this.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Here is one possible way.

20 03 13.xlsm
	A	B	C	D	E	F	G	H	I	J
7	1	2	3	4	5	6	7	Payment	Amount Left	#
8	100	100	90	80	80	80	25	75	30	6
9	100	100	100	100	25	0	0	325	100	1
10	75	75	75	75	75	50	35	250	60	3
11	100	100	100	100	90	80	45	100	25	6
Amount Left

Cell Formulas
Range	Formula
I8:I11	I8	=SUM(INDEX(A8:G8,J8):G8)-H8
J8:J11	J8	=IFERROR(AGGREGATE(14,6,A$7:G$7/((SUM(A8:G8)-SUMIF(A$7:G$7,"<="&A$7:G$7,A8:G8))>H8),1)+1,1)