Excel 2003 - Tracking caps

[Amanda]

I have a dollar amount I track of what I've paid into my company that - after
a certain amount - cap (I don't pay in anymore). I want to track how much I
owe, and when a transaction would cap me, I want it to only show how much I
really paid in, and then any future tranactions show 0 for Dues.

Some sample data of what I'm getting now:

A B C D
Gross Dues Net
Totals 6000 2400 3540

Transaction 1 1000 400 600
Transaction 2 2000 800 1200
Transaction 3 3000 1200 1800
Transaction 4 4000 1600 2400

Lets say that my dues cap at 2000. I want to have a formula that when the
Totals row gets to 2000, Tranaction 3 will show that instead of paying 1200
in, I only paid 800 in, and then have Transaction 4 show 0. However, because
the Totals row is a sum of the Dues row, I get circluar reference errors when
I try to reference it.

Any suggestions? Thanks!

 

[Jacob Skaria]

Suppose you have entries of 400 and 800 in cell C4 and C5. In cell C6 try
this formula and copy down...Here we dont refer the total cell directly
instead get the sum of all cells above..starting from C4.

=2000-SUM($C$4:C5)

If this post helps click Yes

 

[Amanda]

Thanks for the help! It took me a little massaging though to get the dues to
act completely like I wanted.

Corrected Sample Data:

A B C D
Gross Dues Net
Totals 10000 2000 6000

Transaction 1 1000 400 600
Transaction 2 2000 800 1200
Transaction 3 3000 400 1800
Transaction 4 4000 0 2400

I had to adjust your statement to

=IF(((2000-SUM($C$7:C8)))>=(B9*0.4),B9*0.4,IF((2000-SUM($C$7:C8))=0,0,2000-SUM($C$7:C8)))

To get the numbers to not go negative after the cap of 2000 was met. Is
there any cleaner/better way to get this result?

 

[Jacob Skaria]

Try
=IF((2000-SUM($C$7:C8))>=(B9*0.4),B9*0.4,MAX(0,(2000-SUM($C$7:C8)))

If this post helps click Yes

 

[Amanda]

Thanks, that works like a charm!

Amanda

Try
=IF((2000-SUM($C$7:C8))>=(B9*0.4),B9*0.4,MAX(0,(2000-SUM($C$7:C8)))

If this post helps click Yes