Re: Returning NaN in a double array

[Govert van Drimmelen]

Hi Farhan,

Indeed Excel does not display the double.NaN or double.Infinity values
very well. However, even though these are displayed as large numbers,
you can do no calculations on them.

So if you make this function:

public static double getNaN()
{
return double.NaN;
}

and in cell A1 you put =GetNaN(), Excel will display something like:
2.69653970229347E+308
However, try to put in =getNaN() + 0, and Excel will go to a #NUM!
error immediately - it can't actually compute with that large number,
it's just the display routine outputting the double.NaN bits into a
string.
Similar for double.PositiveInfinity.

So if your functions is returning a double, or a double[,] array,
Excel gives us no way to return a value that will be ignored.

But I think you already have the answer -
"If I check for NaN and set the corresponding element in object[,] to
"", Excel shows a value error (assuming this is because the the
objects are of mixed types)."

This is the right plan and you should _not_ be getting an error from
Excel when doing this - if you return an object or object[,] array,
you can return a mixture of types, including some empty strings which
Excel will ignore that value in functions like AVERAGE.

For example, make this function:


public static object get10Blank()
{
return new object[,] {{10}, {""}};
}

Now putting =get10Blank() into as an array formula into cells A1 and
A2, then in A3 I put =AVERAGE(A1:A2) I get the expected result: 10.

Maybe you were not returning the array that was modified with the ""
replacements, or there is some other error in your code causing an
exception (which is returned to Excel as #VALUE)?

Does this make sense?

Regards,
Govert



On Jul 5, 11:07 pm, Farhan Ahmed <Far...@ycharts.com> wrote:
> The UDF I've implemented in ExcelDNA returns an object[,] array wrapped in
> a call to the "resize" function (as available in the samples) since I need
> to be able to change the values of the adjacent cells.
>
> The object[,] is guaranteed to always be object[1,n]. Values are *almost
> always* doubles, except in cases where the requested data does not exist.
> In cases where the data does not exist, I want to return NaN to Excel such
> that this value does not affect the AVERAGE, STDDEV, etc. functions.
>
> If I set the corresponding value in the array to double.NaN, Excel fills
> the cell with a very large value (looks like double.MaxValue) and it screws
> up the calculations. If I check for NaN and set the corresponding element
> in object[,] to "", Excel shows a value error (assuming this is because the
> the objects are of mixed types).
>
> What is the correct way to handle this issue?

[Farhan Ahmed]

Hey Govert,

Thank you for the detailed reply. You know what - replacing the object with a "" works now. I have no idea why it wasn't working for me yesterday - perhaps I was in need of more coffee.

Thanks for your help,

Farhan

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

--

Farhan Ahmed

far...@ycharts.com | ycharts.com

P: 425-802-3818