## PRODUCT FORMULA PROBLEM

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

• Post Options:

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

• Post Options:

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)"

• Post Options:

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;
}

• Post Options:

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

• #### ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• #### ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server