0

I don't know why for some reason the Excel built-in function =SUM() can't really sum up the results created by the custom UDF. It appears to always end up with 0.

For example, I have got a following UDF, called myUDF, which will return the number of items.

For the cell A1:
Formula: =myUDF('ItemA') Result: 10

For the cell B1:
Formula: =myUDF('ItemB') Result: 15

So When I do =Sum(A1:B1) and put the formula in cell C1, it won't return 25 but 0 instead.

I have tried to use some data formatting stuff (converting to numeric) but still no luck there. Has anyone here had similar issue before? Any ideas on the cause of it?

Thanks.

EDIT: Code Sample

        public object MyUDF(string id, string pk, string param1 = "", string param2 = "", string param3 = "", string param4 = "")
        {
            object result = null;
            string strFormula;

            double n = 0;
            DateTime dt;

            try
            {
                strFormula = buildFormula(id, pk, param1, param2, param3, param4);

                result = ws.getServiceResultsDataString(id, objUser, pk, param1, param2, param3, param4);

                if (double.TryParse(result.ToString(), out n)) 
                {
                    result = n;
                }
                else if (DateTime.TryParse(result.ToString(), out dt))
                {
                    result = dt.Date;
                }
                ws.Dispose();
                objUser = null;

            }
            catch (Exception ex)
            {               
            }
            finally
            {   
            }
            return result;
        }
woodykiddy
  • 6,074
  • 16
  • 59
  • 100

1 Answers1

2

Is your UDF returning a number or a string that happens to contain numeric characters?

If I go into Excel and type in '10 (10, formatted as a string) and '15 (15, formatted as a string), and SUM() them, I get 0.

If I type 10 and 15 (formatted as numbers) and SUM() them, I get 25.

Some example code that reproduces the problem would allow us to answer for sure.

Chris Shain
  • 50,833
  • 6
  • 93
  • 125
  • Hmm, good point. Is there a way to convert string value to numeric value in Excel? – woodykiddy Jan 09 '12 at 05:30
  • If your UDF just returns an Int or a Double rather than the String, it shouldn't even need that though. – Chris Shain Jan 09 '12 at 05:34
  • Well, it will return the units along with numeric data in some cases as well. So it's not entirely considered as int type function. – woodykiddy Jan 09 '12 at 05:49
  • 1
    In that case I'd suggest either a) letting it return an Object (which might be a String or an Int or a Double), or b) having 2 (or more) different functions. Varying return types are generally considered confusing at best, so b would be my preference, but you know your use case and I don't. – Chris Shain Jan 09 '12 at 05:57
  • If we let UDF return value be object, will Excel automatically convert the number valued string into numeric value? – woodykiddy Jan 09 '12 at 06:48
  • That sounds like something very easily tested by you. My suspicion is that it won't- you will have to actually return a numeric variable if you want the output to be treated as such. – Chris Shain Jan 09 '12 at 15:24
  • I spent some time rewriting my UDF and now I am using Object as its return type. It worked all right except that Excel will automatically turn the large number, say, 123,456,789, into 1.2345678+E8. Is there any way that I can keep the original format (1000 separator)? – woodykiddy Jan 13 '12 at 05:31
  • Not from the UDF, no. That's a function of excel's default format for large numbers. You can reformat them on the excel sheet as you prefer. – Chris Shain Jan 13 '12 at 05:41
  • I hope that there's a way we can format it programmatically. After all, 123,456,789 is a lot more straightforward than using scientific notations. Anyway, thanks very much all the comments. It's been helpful. – woodykiddy Jan 13 '12 at 05:44