I have a WinGrid and use CalcManager to add several formulas to the columns. Some of the values result in NaN or Infinity values - is there a way to display blank cells or zeros instead of these values in each cell?
There's no really easy way.You could hide the real calculated column and add an unbound column and then use the InitializeRow event to copy he value from one to the other and thus you could change any values you want.
Another option would be to use a CreationFilter and modify the text of the TextUIElement directly.
Is it possible to write the formula such that if the value computes to Infinity/NaN that a different value is displayed, I'm thinking something like in SQL where you can use CASE WHEN.... THEN ... ELSE ... or like IIF.
There is a FormulaErrorValue field - can this be used?
Or perhaps I just need a better understanding of why NaN or Infinity values are being output - I understand "NaN" as not a number, and Infinity - is this caused by a divide by zero error?
You can't use FormulaErrorValue, because NaN is not an error. It's a valid return value for a double.
The formulasdo have support for IF statements. So you could certainly write your formula to return a different value if your calculation results in an NaN value. But whatever you return would have to be supported by the data type of the grid column. You would not return a string into a numeric column, for example.
Hi Mike,
I have a similar problem.
I am buidling a dynamic formula (since column names are not constant) to compute the average and Standard deviation. Text in the cells are displayed as '#NUM!' when all the columns in the formula have Null value.
I want to supress this value and display as Null. How can I achieve that ?
Regards
Ravi
Hi Ravi,
You could use the If function and check for IsError. Something like:
if (isError(average([column]), null(), average([column]) )