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!

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
---------------
Jacob Skaria


"Amanda" wrote:

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!

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" wrote:


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
---------------
Jacob Skaria


"Amanda" wrote:

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!

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
---------------
Jacob Skaria


"Amanda" wrote:

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" wrote:


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
---------------
Jacob Skaria


"Amanda" wrote:

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!

Thanks, that works like a charm!

Amanda

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Amanda" wrote:

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" wrote:


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
---------------
Jacob Skaria


"Amanda" wrote:

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!