Simple forumula/macro help

[JohnnyB99]

I have a simple sum formula with data linked from another workbook I would like for it to carry over back to zero every time the sum reaches 120,000. For example if the value input summed 150000, I would like it to read 30,000, and have some notification that it has went over once. Is this possible with a formula? Do I need to use a macro for this? Thanks for any help/advice.
Best,

 

[Weazel]

The only thing I could come up with would take 2 cells.

if the total of the sum is in A1...try in B1

=IF(A1>120000,SUM(A1-120000))

and ...
in C1

=ROUND(A1/120000,0)

ideally I'd put the "120000" in a separate cell and use a reference to it.

you could also use conditional formatting in lieu of the formula in C1 but thats not going to give you a count, just let you know the value went over 120000.

I'm guessing there is probably a better way to do it but I'm lousy at math and VBA

Hopefully this can get you going in the right direction

Granted this route doesn't take into effect if the original value is less then 120,000

 

[Snakehips]

JohnnyB99,

If you are happy to use two extra cells then you could perhaps do this...

Your existing SUM formula in C1 then Total after reset in B1 Resets in A1

Excel 2007
	A	B	C	D	E	F
1	1	2991	122991	<<< Your Sum Formula in C1
Sheet5

Cell Formulas
Range	Formula
A1	=INT(C1/120000)
B1	=MOD(C1,120000)

Hope that helps.

 

[texasalynn]

to track the number of times it rolls over would really require a macro that increments each time the amount rolls