Hi Jim,
I am afraid that it cannot just multiply the columns directly. You can refer to the following script, which can be put into the Before Formula Scripts panel in the Set Value dialog. This script will get the result row by row after checking the missing values.
range aa = 1; // column 1
range bb = 2; // column 2
range cc = 3; // column 3
range dd = 4; // column 4
for(ii=1; ii<=wks.nrows; ii++)
{
double aa1 = aa[ii]==1/0?1:aa[ii]; // if missing, consider as 1
double bb1 = bb[ii]==1/0?1:bb[ii];
double cc1 = cc[ii]==1/0?1:cc[ii];
if(aa[ii]==1/0 && bb[ii]==1/0 && cc[ii]==1/0) // if all are missing values
dd[ii] = 1/0; // result is missing value
else
dd[ii] = aa1*bb1*cc1; // multiply
}
Penn
Missing Values makes calculation wrong
Posted - 07/12/2012 : 12:16:04 PM
