Formula to arrive at balance amt.

[lalaarif1]

Dear Team,

Is there any formula which gives me the desired out of col D ?

col. a col. b col. c col d
Name |total |amt used |Bal. available
a |500 |100 |400
a |500 |200 |200
a |500 |50 |150
b |1000 |500 |500
b |1000 |700 |0
c |2000 |1500 | 500
c |2000 |500 |0


Please assist since database is huge and its time consuming doing it manually.

 

[PaddyD]

Hi.

With your example data in a1:c8, the following in d2 copied down:

=MAX(0,VLOOKUP(A2,$A$2:$B$8,2)-SUMPRODUCT(--($A$2:A2=A2),$C$2:C2))