Subtracting row-1 data to get next value

[Ekta]

Dear All, 

I am stuck at this formula, appreciate if you can help me please.

The data is like below, i have 3 columns and column A is prepopulate. I need to populate column R and B and they are interrelated.

we will have one row data populated like here data with  40 and 444 but following rows need to be populated with the logic

like for row with A= 126, R = 3000-40 and B with a logic and so on

A	R	B
40	30000	40
126	3000-40	if [A]>[R] then [R] else [A] endif
444
5555
6666
444	50000	if [A]>[R] then [R] else [A] endif
555	50000-444
666

Thank you so much in advance

[atcodedog05]

Hi @Ekta 

Is there any grouping. Is there something like if [previous row]>[current row] then a new group starts and doesnt have calculation [current row] - [previous row] ?

[Ekta]

@atcodedog05 Thank you so much for replying, yeah grouping is based on Student id. there are multiple other columns also.

student id	A	R	B
1	40	30000	40
1	126	3000-40	if [A]>[R] then [R] else [A] endif
1	444
1	5555
1	6666
2	444	50000	if [A]>[R] then [R] else [A] endif
2	555	50000-444
2	666

[atcodedog05]

Hi @Ekta 

Here is how you can do it.

Workflow:

1. Using mult-row formula to calculate R. [row-1:R] - [row-1:A] 

2. Formula to calculate B

Hope this helps : )

[Ekta]

@atcodedog05  oh looks like there is some misunderstanding:(

R = previous R -  B

and then B = if [A]>[R] then [R] else [A] endif

This is repeated will last row for student 1 and then same logic for student 2.

Thank you so much

[atcodedog05]

Hi @Ekta 

Wouldnt B be null before R is calculated?

[atcodedog05]

Hi @Ekta 

Can you show calculation tracing. I am bit confused 😅

[Christina_H]

If I've understood correctly, this is giving the result you want although I'm not calculating it in the same way.

What you seem to be doing is subtracting all values of A for a student from R until there is not enough remaining, then reducing R to 0 by subtracting R instead of A.  I've done that by creating a running sum of A, filling in all values of R with the original value for the student, then subtracting the running sum from the previous row unless it exceeds R, in which case set R to 0 (this doesn't happen in your test data).  I then create B from those values, although depending on what you're doing that might not be necessary.

[Ekta]

@atcodedog05  Thank you so much for your prompt reply.

please find the calculation tracing-

Student Id	A	R	B
1	40	3000	40
1	126	3000-40=2960	if [A]>[R] then [R] else [A] endif -  126
1	444	2960-126=2834	444
1	5555	2834-444=2390	2390
1	6666	2390-2390=0	0
2	444	50000	444
2	555	50000-444=49556	if [A]>[R] then [R] else [A] endif 555
2	666	49556-555=49001	666

Thank you

[atcodedog05 · Accepted solution]

Hi @Ekta 

will this do

workflow:

Hope this helps : )