PRODUCT FORMULA PROBLEM

Posted by: doucette on 8 September 2017, 1:35 pm EST

  • Posted 8 September 2017, 1:35 pm EST

    Hello,


    Im am testing your product and i have the following problem.


    In a cell i have the following formula : PRODUCT(B6,B7)


    When there ise no value in B6 and B7 this formula returns 1


    When in B6 and B7 i input 0 the formula returns 0.


    My question : Why does product return 1 when there is no value in the cells ?


     


    Thanks

  • Replied 8 September 2017, 1:35 pm EST

    The empty cell has a value of Nothing, which the PRODUCT function will not evaluate and hence the bogus 1.  You can use the * operator to multiple string values (provided the
    string values can be converted to number values).  Unlike the PRODUCT
    function, the * operator converts strings values to number values
    before performing the operation.  "(A1 * A2)"

  • Replied 8 September 2017, 1:35 pm EST

    doucette,


    Thanks for bringing this issue to our attention.


    When implementing our functions, we used Excel's documentation as a guideline.  We also tested against Excel's software to try to catch undocumented aspects of Excel's functions.  Unfortunately, it is possible to miss some of those undocumented aspects.


    Excel's documentation for the PRODUCT function states that only numbers in arrays and references are used (i.e. empty cells, boolean values, string values are ignored).  Excel's documentation does not state what happens when there are no number values.  Testing of Excel's software does show that Excel's PRODUCT returns zero when there are no number values (unfortunately we missed catching this fact when we implemented our PRODUCT function).


    Based on Excel's doucmentation, we were using the following basic logic...


        result = 1;
        foreach (Number num in args)
        {
            result = result * num;
        }
        return result;


    Based on your note and additional testing of Excel, we will modify our basic logic to...


        result = 1.0;
        count = 0;
        foreach (Number num in args)
        {
            result = result * num;
            count++;
        }
        if (count = 0)
        {
            return 0.0;
        }
        else
        {
            return result;
        }

  • Replied 8 September 2017, 1:35 pm EST

    doucette,


    The problem has been written up as bug report #23501.  This problem will be fixed in a future maintance release.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels