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