You want to round the CellAmount to something safer before you raise it to a power. 6 decimals should give you enough room, go lower if the error persists (assuming ExponCAGR is not crazy).
Math.Round(CAGRCalc.CellAmount, 6) ^ ExponCAGR - 1.0
If this loss of precision is unacceptable, you can convert the Decimal to a Double by assigning CAGRCalc.CellAmount to a variable of that type, and perform the calculation with this new variable. However, double-based arithmetic is not exactly the same as decimal, so results could be surprising to non-programmers. Generally speaking, we typically recommend to stick to Decimal for financial data.
Hey Jack - this was the solution that Daniel mentioned below. I tried this, and even today by rounding to 0. Still no luck resolving the error.
It seems to be failing at the exponent part of this approach (Dim z). What is odd, it looks like negative value is causing an issue. Testing a Dim with an abs() renders. I guess I could do "IF" logic to adjust, but that's odd to me that I couldn't leverage a negative value on an exponent double?
See examples below:
Dim Double
Result: No longer and error message, but noData being returned.
Making the base value ABS
Result Renders (obviously not the math I want, but the calc runs)
I tried multiple variations and double / rounding conversions (examples above), but no luck. The error does change to my string value: NaN in the error log though.
The following approach is a last resort (string roundtrips should be avoided), but I believe in practice it should be fairly bombproof:
' get the original big number
Dim longNum As Decimal = yourCell.CellAmount ' 0.98765432109876543210987654321
' allocate a variable that will contain the rounded Decimal
Dim x As Decimal
' round the Decimal, convert it to string, then reparse it as Decimal
Decimal.TryParse(Math.Round(longNum, 8).XFToStringForFormula, x)
' do the rest
Return x ^ (1/6) - 1
This effectively ensures that the rounded Decimal is not carrying around allocated zeros that still count, by recreating it completely.
For the record, this is what I'm seeing in my instance (7.2.2):
' this is a valid decimal - signed 28 digits
Dim dc = api.Data.GetDataCell("0.9876543210987654321098765432")
' this is not a valid decimal, would error out
' Dim dc = api.Data.GetDataCell("0.98765432109876543210987654321")
' this works fine
Return dc.CellAmount ^ 99 - 1
If these snippets don't work in your environment, there might be something dodgy in the installation that you might want to discuss with Support.
Could you try storing the values that you're using in the formula as a double prior to the final calculation? I don't really know how it works behind the scenes but wondering if, due to the datatype of the source variables(s), it tries to implicitly store the value as a decimal prior to assigning it to the output variable/cell amount.
So,
dim x as double = someDecimal
dim y as double = someDecimal
dim z as double = x^yJust clutching at straws
This seems to be working.. although, I'm still not exactly sure why I need to call it this way.