Subtracting row-1 data to get next value

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

Accepted answers

atcodedog05

Hi @Ekta

will this do

workflow:

Hope this helps : )

student (1).yxmd

All comments

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 : )

student.yxmd

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.

Subtracting rows.yxmd

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

Hi @Ekta

will this do

workflow:

Hope this helps : )

student (1).yxmd

Christina_H

Those are the results my workflow above gives (with the initial R value for student 1 reset to 3000 rather than 30000), so I believe it's doing what you want it to.

Ekta

@atcodedog05 Thank you so much, works perfectly fine:)

atcodedog05

Happy to help : ) @Ekta

Cheers and have a nice day!

Hollingsworth

Hi @Ekta,

I'm glad you were able to get assistance on this issue. In future though, please limit your tags to those which apply to your issue. This will improve the search results in the community and present your post more efficiently to those who are interested in those tags.

Thanks!

Ekta

@Hollingsworth Thank you so much, i will make sure to limit my tags.

Thank you!

Ekta

@Christina_H Thank you so much for the workflow:)

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335