help...can't get formula to stop calculating after limit reached in a series

[fantabulator]

2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
2500 2500 500 500
1000 2500 200 500


I am trying to track RESP contributions and government grants. In Canada, if you contribute to your child's registered education plan, the gov kicks in 20 percent to a maximum of 500 each year. The government grants end at 7200, but your contribution room can be 50000 over an 18 year period. The first two columns are for the amount of contributions per child. The second two columns calculate the amount of the grant using this formula:=IF(a1<2500,a1*0.2,500) copied 18 times.

My problem is that the grants can be finished in 15 years but contributions can continue until 18 or the 50000 threshold is met.(and the 15th year is capped at 200) I don't know how to get the above formula to stop working once the total grant money = 7200. I tried another if statement but I keep running into problems with loops.

I don't know if the answer is easy or not...just can't seem to see it. Any help would be appreciated.

Thanks.

 

[Hui]

Fantabulator

Firstly, Welcome to the Chandoo.org Forums

Try posting again
You pressed Post Reply too early

 

[bobhc]

Help.....what????????????

 

[Khalid NGO]

Hi,
Welcome to the forum...

If you upload your sample file, it will ease users to understand your actual requirement.
I am not sure about your requirement but see if it helps:

Regards,

 

[fantabulator]

Thanks for taking the time to respond. I looked at your idea but it does not completely fix my problem. After the 7200 threshold is met, I don't want any numbers to appear in the grant boxes. One can still contribute money after that point but it is not matched by the government, so I can't have any money showing up after the threshold is met. In your example the 200 still appears after the 7200 threshold is passed.

I have taken your example and edited it to show my problem...

 

[Somendra Misra]

Hi,

See your file, yellow cells has formula in column E, which will put the grant till 7200. I hope you need this only. If not than write back.

Regards,

 

[fantabulator]

AWESOME...It works...not entirely able to figure it out, but it works...thank you so much for your time!!!

 

[Somendra Misra]

Glad it worked for you, thanks for the feedback and welcome back.

Regards,