Best way to calculate row by row as next row requires calculation in prior row

[nviejep]

Hi there. Probably a simple question compared to some of the others I have seen on here, but I am trying to replicate something similar to the excel spreadsheet above. The expense, income, and net change data is there. However, I need to create the opening, generation(utilization), and closing columns.

The 2018 opening is the only number given. Generation/Utilization is the smaller of opening or absolute value of net change). Closing is open + generation/utilization. And then next year's opening is just prior row's closing.

Is there a simple way to do this? I've been trying to use multiple columns and multi-row/field formulas to no success. Thank you!

[tcroberts]

I've whipped together a solution that first Transposes the data, then uses the Multi-row formula to generate the values, before CrossTabbing back to the original form.

I essentially look for the value of the "name" field that I want to update, and set the value based on position relative to the others. If your columns are not always in the same position, you may want to sort before applying the multirow.

You'll also probably need to tweak the row numbers a bit, as I didn't include the Expense or Income fields, but this will give you the gist of it.

Let me know if this works for you, or you have any questions:

[CharlieS · Accepted solution]

This sheet needs to be solved one year at a time. To achieve this calculation, I built an iterative macro using the [Year] field to control iterations. 

Check out my attached solution and let me know if you have any questions.

[nviejep]

Thank you. This worked great.

[weiweishen]

CharlieS,

I have the same question and I downloaded your workflow. But I cannot see your macro. Is there any way that I can see your macro?

Thank you.

[CharlieS]

---

@weiweishen wrote:

I have the same question and I downloaded your workflow. But I cannot see your macro. Is there any way that I can see your macro?

---

The macros should be included in the .yxzp package. I made an adjustment to the relative references in the attached package  that might work better for you. When you open then package, be sure to open and run "20200227-MultiRowWorkflow.yxmd"

[weiweishen]

Thank you so much. I got the macro and the workflow. Have a great weekend.

[weiweishen]

Charlie

Thank you again for your help.

In my specific case, I want to prepare a Loan Amortization schedule. Please see the attached Excel file. The Excel file shows the result that I want. The # of payment is 20, the only known information is $30,000 book value of the loan, 0.02 annul interest rate and $442.51 fixed payment. From the first payment, the beginning balance is the $30,000 book value, the monthly interest is 30,000*(0.02/12)=50, the ending balance is beginning balance + monthly interest - monthly payment, which is $29,607.49. Now the 29,607.49 become the 2nd payment beginning balance, the exercise will be repeated again and again until to 20 payments. I prepared the following Macro and the workflow. But the iterative macro can only perform the exercise for one period. I don't know whether you can help?

Thank you.

[weiweishen]

Here is the attachments

[CharlieS]

Hi @weiweishen 

It looks like you've got your own use case that isn't related to this topic. I'd suggest using this information to submit at new topic to Designer Discussions. This way it will be a new post that isn't marked as "solved" and you're more likely to get help from the Community.