# Setting Up Threshold Limit Excel Formula

#### andersen_yunan

##### New Member
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?

 Payment Threshold: 1500 Number Payment Made Payment Limit Payment Excess A 1000 1000 0 A 2000 500 1500 A 1500 1500 0 B 2000 1500 500 C 3000 1500 1500 D 2200 1500 700 E 400 400 400 E 1200 1100 100

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

#### andersen_yunan

##### New Member
Sorry, the table should look like this.

 Number Payment Made Payment Limit Payment Excess A 1000 1000 0 A 2000 500 1500 A 1500 0 1500 B 2000 1500 500 C 3000 1500 1500 D 2200 1500 700 E 400 400 400 E 1200 1100 100

<tbody>
</tbody>

Last edited:

#### Fazza

##### MrExcel MVP
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

##### New Member
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

##### New Member
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.

#### Fazza

##### MrExcel MVP
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

##### MrExcel MVP
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

##### New Member
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

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

### Forum statistics

1,082,306
Messages
5,364,410
Members
400,801
Latest member
julievandermeulen

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...