> 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?

> --
> You received this message because you are subscribed to the Google Groups
> "Excel-DNA" group.
> To post to this group, send email to exceldna@googlegroups.com.
> To unsubscribe from this group, send email to
> exceldna+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/exceldna?hl=en.