Hi All, I need help with making a dynamic multi-row formula. Calculation itself is a simple compounding. Where N is the first value(row), [(1+Value(n))*(1+Value(n+1))*(1+Value(n+2))]-1...so on. It is actually equivalent of =FVSCHEDULE(1,Value(n),Value(n+1),...)-1 in EXCEL which also seems to exist in Alteryx as FinanceFVSchedule. The tricky part is that the period (number of rows) is not fixed. It can vary on the workflow outcome. So maybe its 6 rows sometimes, or maybe its 12 rows sometimes. If someone can help me with a formula/method to calculate compounding of changeable number of values(rows) TypeValueDESIRED/EXPECTED OUTCOMEA-0.00419-0.004193025A-0.09329-0.097093214A-0.1191-0.204630879A0.101437-0.12395137A0.036945-0.091586132A0.022284-0.071342618 example: First outcome: -0.00419 +1 = -0.995806975 -1 = -0.004193025 Second outcome: (1+-0.00419)*(1+0.09329) -> (0.995806975 * 0.906708638)-1 = -0.09709 The Main issue is that the number of rows(periods) will change, so it needs to be dynamic. I can't just hard-code rows or fields. Calculation itself isn't very important here. I just need to know how to have a multi-row or multi-column formula that will work dynamically even if the number of rows or columns change. If there is way to do this by column instead of rows, that's even better. Thank you so much for your time. Example.xlsx

Dynamic Multi-Row Formula

Hi All,

I need help with making a dynamic multi-row formula.

Calculation itself is a simple compounding. Where N is the first value(row), [(1+Value(n))*(1+Value(n+1))*(1+Value(n+2))]-1...so on. It is actually equivalent of =FVSCHEDULE(1,Value(n),Value(n+1),...)-1 in EXCEL which also seems to exist in Alteryx as FinanceFVSchedule.

The tricky part is that the period (number of rows) is not fixed. It can vary on the workflow outcome. So maybe its 6 rows sometimes, or maybe its 12 rows sometimes.

If someone can help me with a formula/method to calculate compounding of changeable number of values(rows)

Type	Value	DESIRED/EXPECTED OUTCOME
A	-0.00419	-0.004193025
A	-0.09329	-0.097093214
A	-0.1191	-0.204630879
A	0.101437	-0.12395137
A	0.036945	-0.091586132
A	0.022284	-0.071342618

example:

First outcome: -0.00419 +1 = -0.995806975 -1 = -0.004193025

Second outcome: (1+-0.00419)*(1+0.09329) -> (0.995806975 * 0.906708638)-1 = -0.09709

The Main issue is that the number of rows(periods) will change, so it needs to be dynamic. I can't just hard-code rows or fields.

Calculation itself isn't very important here. I just need to know how to have a multi-row or multi-column formula that will work dynamically even if the number of rows or columns change.

If there is way to do this by column instead of rows, that's even better.

Thank you so much for your time.

Example.xlsx

Workflow

Tips and Tricks

Dynamic Processing

Accepted answers

mceleavey

@mjh418 ,

Ah, I understand now. It's easier for me to see it when I can see the calculation flow.

I've attached my workflow, not sure if it's the same as @Pingu , I used a simple Multi-Row formula.

Obviously you can mess around with the rounding issue, but I think the calculation is what you need:

Hope this helps,

FVSCHEDULE.yxmd

mceleavey

@mjh418 ,

I've amended the XML to your version. Try it now.

GroupedRecordId.yxmc

All comments

mceleavey

@mjh418 ,

How did you get the first desired outcome? This is more decimal places, and so the calculation will be out.

mjh418

the decimal places don't matter too much, just rounding matter.

Formula is just compounding. First row Value +1 multiply it to second row value + 1, multiply it by third row value +1.. and so on.

mceleavey

@mjh418 ,

I'm confused.

Row 1 value +1 = 0.99581.

Row 2 value +1 = 0.90671.

0.99581*.90671= 0.90291

Desired outcome = -0.004193

I guess I'm not understanding your requirements. Can you explain what would be the calculation to arrive at -0.004193 for row 1?

Pingu

If possible could you just post an Excel with the Formula, I had the same issue as mceleavey. I can also not land on your numbers in green.

As I understand row 1 = row 1 as it is the first value.

But whatever I tried I could not generate second row -0.097093214

mjh418

1. First row outcome will be always same as the value because its not compounding into anything.
2. 0.99581*.90671= 0.90291 -1 = -0.09709 which is the desired outcome of value 1 and 2 compounded. Sorry I forgot to mention that you have to subtract the result by 1.

3. Excel Formula is =FVSCHEDULE(1,Value1,Value2...)-1

mceleavey

@mjh418 ,

"0.99581*.90671= 0.90291 -1 = -0.09709 which is the desired outcome of value 1 and 2 compounded"

Can you show us where you're getting those numbers as they don't exist.

Please post the Excel sheet so we can see the calculations.

mjh418

0.90291 -1 does equal to -0.09709. I used the numbers you wrote in your post.
Not sure what you mean by number doesn't exist....this is a common formula used to compound anything.

I posted excel sheet as per request in the post. Thank you for your time.

Pingu

To test grouping or dynamic types it would maybe better to have a larger sample set.

What about attached workflow, does it help with your needs?

Edit: small change to workflow

Finance too long ago.yxmd

mceleavey

@mjh418 ,

Ah, I understand now. It's easier for me to see it when I can see the calculation flow.

I've attached my workflow, not sure if it's the same as @Pingu , I used a simple Multi-Row formula.

Obviously you can mess around with the rounding issue, but I think the calculation is what you need:

Hope this helps,

FVSCHEDULE.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703