I ended up having a formula which had to apply the PRODUCT function on the daily performances to calculate the aggregated month performance.
The daily performances could be both positive and negative:
| Day | Perf |
|---|
| 01.01.2016 | 1% |
| 02.01.2016 | -1% |
| 03.01.2016 | 5% |
| 04.01.2016 | -20% |
| 05.01.2016 | 10% |
| 06.01.2016 | 6% |
I could not use $(=Concat(Perf,'*')) as I was using a Pivot table where for each level the aggregations should have been on different fields.
Exp(Sum(Log(Perf))) works fine only on positive numbers: Log on negative values produces an error and Sum skips these errors.
I'd like to share the formula for finding the PRODUCT of all the numbers, positive and negative included.
The final formula is the following:
Exp( // PRODUCT function
Sum(
Log(
Fabs(Perf) //Treat all values as if they were positive
)
)
)
* ( // Determine final sign
mod(
sum(
sign(
sign(Perf) - 1
)
), 2
) * -2 + 1
)
The approach is this: execute the PRODUCT function on all the numbers converted to positive numbers and multiply it to 1 or -1 according to what is necessary.
| Function | Explanation |
|---|
| sign(Perf) - 1 | Remove positive signs: (-1, 0, 1) - 1 => (-2, -1, 0) |
| sign( sign(Perf) - 1 ) | Convert all negatives to -1; zeros remain 0 |
| sum(...) | Sum up all the negatives to determine if they are odd or even |
| mod(..., 2) | Check if the final number of negative values is odd or even |
| mod(..., 2) * -2 + 1 | Convert mod result (1 or 0) into (-1 or 1) |
The final expression sounds like: PRODUCT([Positive values]) * [-1 if odd negative values or 1 if even negative values].
Is there any better way to doing this?
