Setting Up Threshold Limit Excel Formula

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
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!

Payment Threshold: 1500
NumberPayment MadePayment LimitPayment Excess
A100010000
A20005001500
A150015000
B20001500500
C300015001500
D22001500700
E400400400
E12001100100

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

andersen_yunan

New Member
Joined
Feb 7, 2018
Messages
36
Sorry, the table should look like this.

NumberPayment MadePayment LimitPayment Excess
A100010000
A20005001500
A150001500
B20001500500
C300015001500
D22001500700
E400400400
E12001100100

<tbody>
</tbody>
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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
Joined
Feb 7, 2018
Messages
36
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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
Joined
May 17, 2006
Messages
9,192
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
Joined
Feb 7, 2018
Messages
36
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
Joined
May 17, 2006
Messages
9,192
seems like formulas, with or without VBA, are going to take a long time
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top