Calculating value based on multiple column conditioning

[Ekta]

Dear All, 

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

Column A and B are already populated , we need to calculate C , D and E and each column is dependent on other columns, below is the calculation tracing for reference pls.

Student	A	B	C	D	E
1	40	3000	3000	lower of A and C -  40	First Row for each student = 0
1	126	3000	3000-40	lower of A and C  -  126	40+0 = 40
1	40	3000	3000-166	lower of A and C -  40	40+126  =166
1	10	3000	3000-206	lower of A and C -  10	166+40 = 206
1	20	3000	3000-216	lower of A and C -  20	206+10 =216
2	30	3000	3000	lower of A and C-  30	First Row for each student = 0
2	40	3000	3000-30	lower of A and C -  40	30+0 = 30
2	10	3000	3000-70	lower of A and C -  10	30+40=70

Thank you so much in advance

[atcodedog05]

Hi @Ekta 

Here is how you can do it.

Workflow:

1. Using mult-row formula tool to calculate E for each group.

2. Using formula tool to calculate C and D.

Hope this helps : )

[Ekta]

@atcodedog05 Thanks so much for the workflow, i would like to flag here for calculating E, we need to add 0 from E(first row) and 40 from D(first row) column.

please let me know how can we do this?

Best Regards

[atcodedog05 · Accepted solution]

Hi @Ekta 

The above solution follows a similar logic. 

Here is a workflow which follows exact logic.

Hope this helps : )

[Ekta]

@atcodedog05 Thank you so much for the workflow but some how i am not able to get the correct answer.I am trying to make some adjustments to the workflow, but no success yet:(

Actually, calculation need to be done rowwise, means first for row 1 for student id 1 - E=0 then C need to be calculated which is B-E  then comes D which is lower of A and C.

then we calculate row 2 where first we calculate E2 = E(row1)+D(row1) then we calculate C = B-E and then D which is lower of A and C.

similarly for row 3, first calculate E3=E(row2)+D(row2) then we calculate C = B-E and then D which is lower of A and C.

then we calculate next row and so on till last record of student id 1 and repeat the whole process for next student.

where E for student id 2 = 0

I am unsure what mistake i may be making.

looking forward to hearing from you.

Regards

[atcodedog05]

Hi @Ekta 

Can you share me scenarios of where its failing? I can take a look into it.

[danilang]

Hi @Ekta 

In future you'll find that there is much less back and forth(and it will make our job easier) if you provide the not only the rules, but also a sample of the output as well

Thanks

Dan  

[Ekta]

@atcodedog05 Thank so much, i could manage to get the correct output from the workflow.

Thank you so much, Appreciate your help and guidance.