Dynamic Multi-Row Formula

[mjh418]

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.

[mceleavey]

@mjh418 ,

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

M.

[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.

M

[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

[mceleavey · Accepted solution]

@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,

M.