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 ABCDE14030003000lower of A and C - 40First Row for each student = 0112630003000-40lower of A and C - 12640+0 = 4014030003000-166lower of A and C - 4040+126 =16611030003000-206lower of A and C - 10166+40 = 20612030003000-216lower of A and C - 20206+10 =21623030003000lower of A and C- 30First Row for each student = 024030003000-30lower of A and C - 4030+0 = 3021030003000-70lower of A and C - 1030+40=70 Thank you so much in advance

Calculating value based on multiple column conditioning

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

Common Use Cases

Best Practices

Accepted answers

atcodedog05

Hi @Ekta

The above solution follows a similar logic.

Here is a workflow which follows exact logic.

Hope this helps : )

students.yxmd

All comments

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

students.yxmd

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

Hi @Ekta

The above solution follows a similar logic.

Here is a workflow which follows exact logic.

Hope this helps : )

students.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703