Subtract an amount from a cell, then move and subtract from the next available amount

Digit4L

New Member
Joined
Feb 28, 2019
Messages
2
Dear All,

I have tried to subtract the Overdue Invoice Amount, It should go like below table.

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
QRS
T
U
V
W
X
Y
Z
AA
AB
2
AMT $
TTL DUE
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
PAID
OD BAL. TTL
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
3
XYZ INC
44
0
0
0
0
0
20
0
14
0
0
10
0
25
19
0
0
0
0
0
0
0
9
0
0
10
0

<tbody>
</tbody>



FORMULA USED
Q3
=MAX(SUM(C3:C3)-O3,0)
R3
=IF(OR(Q3>0, D3=""),"",MAX(SUM(C3:D3)-O3,0))
S3
=IF(OR(R3>0, E3=""),"",MAX(SUM(C3:E3)-O3,0))
T3
=IF(OR(S3>0, F3=""),"",MAX(SUM(C3:F3)-O3,0))
U3
=IF(OR(T3>0, G3=""),"",MAX(SUM(C3:G3)-O3,0))
V3
=IF(OR(U3>0, H3=""),"",MAX(SUM(C3:H3)-O3,0))
W3
=IF(OR(V3>0, I3=""),"",MAX(SUM(C3:I3)-O3,0))
X3
=IF(OR(W3>0, J3=""),"",MAX(SUM(C3:J3)-O3,0))
Y3
=IF(OR(X3>0, K3=""),"",MAX(SUM(C3:K3)-O3,0))
Z3
=IF(OR(Y3>0, L3=""),"",MAX(SUM(C3:L3)-O3,0))
AA3
=IF(OR(Z3>0, M3=""),"",MAX(SUM(C3:M3)-O3,0))
AB3
=IF(OR(AA3>0, N3=""),"",MAX(SUM(C3:N3)-O3,0))

<tbody>
</tbody>

But I am getting the below, Till Aug its fine, but after Aug it showing empty.
Totally Struck, Looking for support.

1
A
B
C
D
E
F
G
H
I
J
K
L
MNOP
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
2
AMT $
TTL DUE
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
PAID
OD BAL>
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
3
XYZ INC
44
0
0
0
0
0
20
0
14
0
0
10
0
25
9
0
0
0
0
0
0
0
9





<tbody>
</tbody>

Thanks a lot,
 

queuesmef

New Member
Joined
Dec 19, 2016
Messages
20
Why not just Sum C through O and make your payment amount negative? Then it's just the sum of all of those combined?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,398
Office Version
365
Platform
Windows
How about in R3 copied right
=IF(D3=0,0,MAX(SUM($C3:D3)-$O3,0)-SUM($Q3:Q3))
 

Forum statistics

Threads
1,082,504
Messages
5,365,951
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top