Add values in a column to a number and then start again

[lilcheung]

Hi, I have a column of numbers and I need to find a way for the column beside it to add them up and stop at 1000 and then start again. For example:

100
24
56
320
80

I need to somehow add 100+24+56...up to 350 using a portion of the 320 (170 to get to 350) and then start again by applying the rest of the 320 amount of 150+80+...to get to 350 again.

Is there a formula or do I need to use a code? I don't know how to use VBA.

[MarvinP]

Hi lilcheung and welcome to the forum.

I think a MOD function will do your problem. See the attached with some random data. Column B is only for checking. Column C has the answer in it.

[lilcheung]

Thank you MarvinP. This is great help. Another request...if I want to show the remaining amount to make up to 1000 (ie the 288 out of the 648 in your example), what function will I use? Would I put it in ColD? or is there a way to show it in Col C too? Thank you in advance for your help!!

[MarvinP]

See if this is what you want in Col D

[lilcheung]

Thank you Marvin P. Really appreciate your help!! ;-)