Pick value each month until maximum is met

[rajkenya1]

In the attached spread sheet i have in cell C6 a monthly client uptake and in cell C7 maximum clients 490. I want in cells say D3 to Q3 and beyond to pick the monthly clients update until it reaches maximum and then zero after that. So say in cell M9 it should be the balance i.e 490 less the sum of 450 which is 40 and then zero thereafter. Please help

 

[bosco_yip]

Try.........

In D3, formula copied across to Q3 :

=IF(COUNT($D2:D2)=ROUNDUP($C7/$C6,0),$C7-SUM($C3:C3),IF(COUNT($D2:D2)<ROUNDUP($C7/$C6,0),$C6,0))

Regards
Bosco

 

[rajkenya1]

Thank you Bosco. You are a legend. An absolute genius. Thank you.

 

[Harshad Maloo]

=IF($C$7-(SUM($C$5:C$5))>50,50,$C$7-SUM($C$5:C$5))

 

[John Jairo V]

Hi, to all!

Another option could be:
[D3] : =MAX(,MIN($C6,$C7-SUM($C3:C3)))

And drag it to right. Blessings!

 

[rajkenya1]

Thank you so much my friend. Really good and very different. Well done. You are a genuis. Thank you.