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.
