Setting Up Threshold Limit Excel Formula

[andersen_yunan]

Hi All, currently I have problem to solve this issue regarding setting up formula for case below.
As you can see, I have a payment threshold of $1500, while the original data that I have is only the column #number & #payment made. I want to have a formula that can generate the value in column #payment limit & #payment excess, which the total of payment limit should not exceed given payment threshold for each number.

Is there any excel formula that can solve this?

Thanks in advance!

[TABLE="width: 377"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2"]Payment Threshold: 1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD]Payment Made[/TD]
[TD]Payment Limit[/TD]
[TD]Payment Excess[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[andersen_yunan]

Sorry, the table should look like this.

[TABLE="width: 377"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Payment Made[/TD]
[TD]Payment Limit[/TD]
[TD]Payment Excess[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

in C2, =MAX(0,MIN(1500,SUMIF($A$1:A2,$A2,$B$1:B2))-SUMIF($A$1:A1,A2,$B$1:B1))
in D2, =B2-C2

fill down
OK?

 

[andersen_yunan]

in C2, =MAX(0,MIN(1500,SUMIF($A$1:A2,$A2,$B$1:B2))-SUMIF($A$1:A1,A2,$B$1:B1))
in D2, =B2-C2

fill down
OK?

Thanks! The formula works!

 

[andersen_yunan]

Hi Fazza,

I tried to run your formula in my sheet, however it takes so much time >40 minutes since I have around 200k rows.

Is there any way I can make this faster? I was thinking of VBA but it doesn't help with copy and paste value only resulted in copy of value results in first cell only instead of all.
Thanks in advance!

 

[Fazza]

yes, more than 40 minutes is too long
the proposed formulas are simple & should be adequately quick
likely it is something other than these formulas

does the spreadsheet purely have data values in two columns?

or,
are there any other formulas in the spreadsheet?
or conditional format?
or VBA?
or other obvious slow points?

 

[Fazza]

so I tested this on my work laptop : it is a few years old

200,000 records was too much for it to calculate!

so i tried just putting the first formula halfway down - 100,000 cells.
100,000 records in column C only took about 2 to 3 minutes.
then pasted this to values only, took another couple of minutes.

on that basis, it would take another similar ~5 minutes to do the second 100,000 formulas, column C only. pasted to values.
so column C for 200,000 records in 10 minutes
then I'm guessing a lesser duration to do the simple subtraction in column D. I didn't do this step
This suggests whole exercise would take close to 20 minutes.

still seems too slow to me

maybe you'll find it quicker on a newer computer (that is, doing one column at a time, and half the records at a time. Given your computer can do the 200,000 records at all, it is sure to be faster than my oldish work computer)
Or changing the column C formula to a couple of steps using a helper column instead of in the single column. this is likely the best you can do with formulas

 

[andersen_yunan]

so I tested this on my work laptop : it is a few years old

200,000 records was too much for it to calculate!

so i tried just putting the first formula halfway down - 100,000 cells.
100,000 records in column C only took about 2 to 3 minutes.
then pasted this to values only, took another couple of minutes.

on that basis, it would take another similar ~5 minutes to do the second 100,000 formulas, column C only. pasted to values.
so column C for 200,000 records in 10 minutes
then I'm guessing a lesser duration to do the simple subtraction in column D. I didn't do this step
This suggests whole exercise would take close to 20 minutes.

still seems too slow to me

maybe you'll find it quicker on a newer computer (that is, doing one column at a time, and half the records at a time. Given your computer can do the 200,000 records at all, it is sure to be faster than my oldish work computer)
Or changing the column C formula to a couple of steps using a helper column instead of in the single column. this is likely the best you can do with formulas

Hi Fazza, Thanks for the input!
Will try this approach. At first, I though VBA could solve this but it's still stuck in the calculation part as well.

 

[Fazza]

seems like formulas, with or without VBA, are going to take a long time