INCREMENTALLY DECREASE A THRESHOLD VALUE TILL A CONDITION IS MET

Hi Everyone,

This is a repeat question as i couldn't get an answer earlier. Below is the workflow stage I'm at.

After this stage, I have to check for the sum of New Delta. If it's above 20k, the threshold of 'abs([Delta Adj])' has to be decreased 500 every time until the sum of New Delta is below 20k.

I believe we need to use a iterative macro here. As I haven't worked using macro, would be grateful to anyone who can assist on the same.

Thanks in advance

Macros

Iterative Macro

Accepted answers

AncientPandaman

here the update version. add summary in case you want to see.

20k.yxzp

All comments

JamesCharnley

@Shahas If you want to use an iterative macro I can share the general logic here but you'd need to change the template to match your schema. But the macro will effectively check whether or not that value is >= 20000, and if it is then it will continue to iterate. Once that condition is not met, your value will leave the other output.

Delta ADJ.yxmc

Shahas

heyy @JamesCharnley Thanks for the response.

Correct me if I'm wrong here, but the filter will just be checking for row by row values, and if they are above 20k, it performs the loop condition.

What I would like to have is to check whether the sum of new Delta is above 20k and if so, delta adj threshold should be reduced incrementally by 500 till the sum of New delta becomes less than 20k.

Hope its clear

AncientPandaman

if it contains sensitive data, you can mock some sample data, and illustrate how it goes.

if not, it just waste all of us time for guessing.

you also can see that no body want to answer your question, it wasted time to guess your data, rebuild data and etc.

here is my understand, the 20k is limit on total, and -500 for all rows adj delta if exceed.

another guess, minimum of adj delta is 0,

the another guess, iteration stop adding if reach to 0.

add iteration column for stop iteration increase if met criteria.

sum new delta, append back, reduce adj delta, remove columns for iteration.

20k_no mock data.yxzp

Shahas

heyy @PangHC The dataset looks like below :

Here, as u can see, Delta Adj values are filtered to be less than 20k each. After this step is done, I need to check the sum of New Delta, and if its above 20k, Delta Adj value threshold of 20k has to be reduced by 500 to 19500 and again check for the sum of New Delta. This process has to be repeated till the sum of New Delta reaches below 20k.

FYI : (NEW DELTA = DELTA ORIGINAL + DELTA ADJ)

Hope this brings a bit of clarity

AncientPandaman

@Shahas did my attached file help?

and based on your data.

Total first 2 line already exceed . so it should reduce till negative value?

i required more detail mock sample in excel/table and explanation, not screenshot. for raw data, 1st round, and the final outcome.

For example:

Ori

Ori Delta	Adj Delta	New Delta
20000	2400	22400
8000	2000	10000

total new delta exceeds 20k, reduce each adj delta for 500

after 1st round macro

Ori Delta	Adj Delta	New Delta
20000	1900	21900
8000	1500	9500

still over 20k, continue till

Ori Delta	Adj Delta	New Delta
20000	-4100	15900
8000	-4500	3500

Shahas

@PangHC Apologies for the late reply.

The logic implemented in the example is exactly what I require. You can use the attached mock data :

Original delta	Delta Adj	New Delta
21000	4500	25500
15000	3200	18200
4566	2500	7066
3245	1456	4701
5740	4000	9740
9500	3000	12500
2250	2000	4250
1245	3256	4501
3500	1000	4500

AncientPandaman

here the update version. add summary in case you want to see.

20k.yxzp

Shahas

@PangHC That seems to work.. Thank u so much. really appreciated.

Shahas

@PangHC just a small request though..

Correct me if I'm wrong. Here I believe we are reducing values of Delta Adj by 500 in each row...

instead of that, I just need the threshold to be reduced by 500

at first, the threshold of Delta Adj is kept as < 20000. So only those rows which are having values less than 20k are selected. then sum of New Delta is checked and if it is above 20k, threshold has to be reduced by 500. So then only rows which are less than 19500 are selected and so on..

Hope its clear

AncientPandaman

@Shahas, yes, we are reducing values of Delta Adj by 500 in each row.

and, I tired on guessing. I more preferred tables.

Please provide tables in every step, with explanation.

Shahas

@PangHC heyy... I will try to explain step by step.

After initial basic steps, I had to apply a threshold based on Delta Adj, ie, if its < 20k, the value will be 0 and rest every rows will be kept as it is.

This is the way I did it. At this point, data looks like this :

Original delta	Delta Adj
35000	35000
33568	33568
30000	30000
28000	28000
19450	0
18700	0
12200	0
9000	0
3500	0

Then the formula is applied, New Delta = Delta Adj+ Original Delta

Now the data looks like this :

Original delta	Delta Adj	New Delta
35000	35000	70000
33568	33568	67136
30000	30000	60000
28000	28000	56000
19450	0	19450
18700	0	18700
12200	0	12200
9000	0	9000
3500	0	3500

Here onwards, we have to check the sum of New Delta. If its above 20k, the threshold which we applied in the 1st screenshot has to be reduced by 500 to 19500 and so on till we have the sum of new Delta < 20k.

So what I require is to have the threshold to be reduced incrementally till the sum of New Delta is below 20k.

Let me know if anything is not clear or any further details are required

AncientPandaman

@Shahas

if I understand correctly.

at start, threshold for rows 20k

if total new delta >20k, threshold for rows will -500 to 19,500

until total new delta <=20k?

Original delta	Delta Adj	New Delta	remark
35000	35000	70000	delete as new delta more than 20k
33568	33568	67136	delete as new delta more than 20k
30000	30000	60000	delete as new delta more than 20k
28000	28000	56000	delete as new delta more than 20k
19450	0	19450	sum for first round, as threshold is 20k
18700	0	18700	sum for first round, as threshold is 20k
12200	0	12200	sum for first round, as threshold is 20k
9000	0	9000	sum for first round, as threshold is 20k
3500	0	3500	sum for first round, as threshold is 20k

till 3rd round, 19450 is exclude as threshold now is 19000, but sum of new delta is still 20k.

Original delta	Delta Adj	New Delta	remark
19450	0	19450	delete as new delta more than 19000
18700	0	18700	remain as threshold is 19000
12200	0	12200	remain as threshold is 19000
9000	0	9000	remain as threshold is 19000
3500	0	3500	remain as threshold is 19000