rework handling of empty cells in opencl code

Some of the code handling ranges of cells wants empty cells to be zero,
some wants to skip them, and few want special handling. So just make
three generic cases that handle these, which somewhat simplifies this
while still allowing flexibility where needed. Also handle better
Test::testFuncSUMXMY2, which works on a pair of ranges, sets a cell
in one to a value and another is empty, in this case it is necessary
to iterate over this pair with SkipEmpty even if for the second one
it's beyond GetArrayLength().

Change-Id: I6c8edaaadb02ffe2a6a7a9399347909008ea188e
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/140249
Tested-by: Jenkins
Reviewed-by: Luboš Luňák <l.lunak@collabora.com>

8 files changed, 317 insertions, 325 deletions

diff --git a/sc/source/core/opencl/op_array.cxx b/sc/source/core/opencl/op_array.cxx
index 7369c1ed44bf..47ae15218588 100644
--- a/sc/source/core/opencl/op_array.cxx
+++ b/sc/source/core/opencl/op_array.cxx
@@ -26,9 +26,8 @@ void OpSumX2MY2::GenSlidingWindowFunction(outputstream &ss,
     ss << "{\n";
     ss << "    int gid0=get_global_id(0);\n";
     ss << "    double tmp =0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if( !isnan(arg1) && !isnan(arg2))\n"
-        "            tmp +=pow(arg1,2) - pow(arg2,2);\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, EmptyIsZero,
+        "        tmp +=pow(arg1,2) - pow(arg2,2);\n"
         );
     ss << "    return tmp;\n";
     ss << "}\n";
@@ -44,9 +43,8 @@ void OpSumX2PY2::GenSlidingWindowFunction(outputstream &ss,
     ss << "{\n";
     ss << "    int gid0=get_global_id(0);\n";
     ss << "    double tmp =0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if( !isnan(arg1) && !isnan(arg2))\n"
-        "            tmp +=pow(arg1,2) + pow(arg2,2);\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, EmptyIsZero,
+        "        tmp +=pow(arg1,2) + pow(arg2,2);\n"
         );
     ss << "    return tmp;\n";
     ss << "}\n";
@@ -62,9 +60,8 @@ void OpSumXMY2::GenSlidingWindowFunction(outputstream &ss,
     ss << "{\n";
     ss << "    int gid0=get_global_id(0);\n";
     ss << "    double tmp =0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if( !isnan(arg1) && !isnan(arg2))\n"
-        "            tmp +=pow((arg1-arg2),2);\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, EmptyIsZero,
+        "        tmp +=pow((arg1-arg2),2);\n"
         );
     ss << "    return tmp;\n";
     ss << "}\n";
diff --git a/sc/source/core/opencl/op_financial.cxx b/sc/source/core/opencl/op_financial.cxx
index 92263d78abb4..1d4efa00a9f3 100644
--- a/sc/source/core/opencl/op_financial.cxx
+++ b/sc/source/core/opencl/op_financial.cxx
@@ -347,9 +347,8 @@ void Fvschedule::GenSlidingWindowFunction(
     ss << "int gid0 = get_global_id(0);\n";
     GenerateArg( 0, vSubArguments, ss );
     ss << "\t";
-    GenerateRangeArg( 1, vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "            tmp *= arg + 1;\n"
+    GenerateRangeArg( 1, vSubArguments, ss, SkipEmpty,
+        "        tmp *= arg + 1;\n"
         );
     ss << "\t";
     ss << "return (double)tmp * arg0";
@@ -420,13 +419,10 @@ void OpIRR::GenSlidingWindowFunction(outputstream &ss,
     ss << "    while (fEps > Epsilon && nItCount < 20)\n";
     ss << "    {\n";
     ss << "        nCount = 0.0; fNumerator = 0.0;  fDenominator = 0.0;\n";
-    GenerateRangeArg( 0, vSubArguments, ss,
-        "            if (!isnan(arg))\n"
-        "            {\n"
-        "                fNumerator += arg / pow(1.0 + x, nCount);\n"
-        "                fDenominator+=-1*nCount*arg/pow(1.0+x,nCount+1.0);\n"
-        "                nCount += 1;\n"
-        "            }\n"
+    GenerateRangeArg( 0, vSubArguments, ss, SkipEmpty,
+        "            fNumerator += arg / pow(1.0 + x, nCount);\n"
+        "            fDenominator+=-1*nCount*arg/pow(1.0+x,nCount+1.0);\n"
+        "            nCount += 1;\n"
         );
     ss << "        xNew = x - fNumerator / fDenominator;\n";
     ss << "        fEps = fabs(xNew - x);\n";
@@ -453,10 +449,9 @@ void XNPV::GenSlidingWindowFunction(
     ss << "    double result = 0.0;\n";
     ss << "    int gid0 = get_global_id(0);\n";
     GenerateArg( "rate", 0, vSubArguments, ss );
-    GenerateRangeArgElement( "dateNull", 2, "0", vSubArguments, ss );
-    GenerateRangeArgPair( 1, 2, vSubArguments, ss,
-        "        if( !isnan(arg1) && !isnan(arg2))\n"
-        "            result += arg1/(pow((rate+1),(arg2-dateNull)/365));\n"
+    GenerateRangeArgElement( "dateNull", 2, "0", vSubArguments, ss, EmptyIsZero );
+    GenerateRangeArgPair( 1, 2, vSubArguments, ss, SkipEmpty,
+        "        result += arg1/(pow((rate+1),(arg2-dateNull)/365));\n"
         );
     ss << "    return result;\n";
     ss << "}";
@@ -541,23 +536,20 @@ void OpMIRR::GenSlidingWindowFunction(
     ss << "int nCount = 0;\n\t";
     ss << "bool bHasPosValue = false;\n";
     ss << "bool bHasNegValue = false;\n";
-    GenerateRangeArg( 0, vSubArguments, ss,
-        "        if (!isnan(arg))\n"
+    GenerateRangeArg( 0, vSubArguments, ss, SkipEmpty,
+        "        if (arg > 0.0)\n"
         "        {\n"
-        "            if (arg > 0.0)\n"
-        "            {\n"
-        "                NPV_reinvest += arg * Pow_reinvest;\n"
-        "                bHasPosValue = true;\n"
-        "            }\n"
-        "            else if (arg < 0.0)\n"
-        "            {\n"
-        "                NPV_invest += arg * Pow_invest;\n"
-        "                bHasNegValue = true;\n"
-        "            }\n"
-        "            Pow_reinvest /= reinvest;\n"
-        "            Pow_invest /= invest;\n"
-        "            nCount++;\n"
+        "            NPV_reinvest += arg * Pow_reinvest;\n"
+        "            bHasPosValue = true;\n"
         "        }\n"
+        "        else if (arg < 0.0)\n"
+        "        {\n"
+        "             NPV_invest += arg * Pow_invest;\n"
+        "             bHasNegValue = true;\n"
+        "        }\n"
+        "        Pow_reinvest /= reinvest;\n"
+        "        Pow_invest /= invest;\n"
+        "        nCount++;\n"
         );
     ss << "if ( !( bHasPosValue && bHasNegValue ) )\n";
     ss << "    return CreateDoubleError(IllegalArgument);\n";
@@ -867,17 +859,14 @@ void OpNPV::GenSlidingWindowFunction(outputstream &ss,
     ss << "    int gid0 = get_global_id(0);\n";
     ss << "    int nCount = 1;\n";
     GenerateArg( 0, vSubArguments, ss );
-    GenerateRangeArgs( 1, vSubArguments.size() - 1, vSubArguments, ss,
+    GenerateRangeArgs( 1, vSubArguments.size() - 1, vSubArguments, ss, SkipEmpty,
         "        double temp1=1.0;\n"
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            for(int i=1;i<nCount;i+=2)\n"
-        "                temp1*=pow(1.0f + arg0,2);\n"
-        "            if(nCount%2)\n"
-        "                temp1*=1.0f + arg0;\n"
-        "            tmp += arg / temp1;\n"
+        "        for(int i=1;i<nCount;i+=2)\n"
+        "            temp1*=pow(1.0f + arg0,2);\n"
+        "        if(nCount%2)\n"
+        "            temp1*=1.0f + arg0;\n"
+        "        tmp += arg / temp1;\n"
         "        nCount += 1;\n"
-        "        }\n"
         );
     ss << "    return tmp;\n";
     ss << "}";
@@ -1862,8 +1851,8 @@ void OpXirr::GenSlidingWindowFunction(outputstream &ss,
     ss << "    bool bContLoop = false;\n";
     ss << "    bool bResultRateScanEnd = false;\n";
     // Make 'V_0' and 'D_0' be the first elements of arguments 0 and 1.
-    GenerateRangeArgElement( "V_0", 0, "0", vSubArguments, ss );
-    GenerateRangeArgElement( "D_0", 1, "0", vSubArguments, ss );
+    GenerateRangeArgElement( "V_0", 0, "0", vSubArguments, ss, EmptyIsZero );
+    GenerateRangeArgElement( "D_0", 1, "0", vSubArguments, ss, EmptyIsZero );
     ss << "    do\n";
     ss << "    {\n";
     ss << "        if (nIterScan >=1)\n";
@@ -1872,18 +1861,14 @@ void OpXirr::GenSlidingWindowFunction(outputstream &ss,
     ss << "        {\n";
     ss << "            double r = fResultRate + 1;\n";
     ss << "            fResultValue = V_0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "                if(!isnan(arg1) && !isnan(arg2))\n"
-        "                    fResultValue += arg1/pow(r,(arg2 - D_0)/365.0);\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "                fResultValue += arg1/pow(r,(arg2 - D_0)/365.0);\n"
         , "1" // start from 2nd element
         );
     ss << "            double fResultValue2 = 0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "                if(!isnan(arg1) && !isnan(arg2))\n"
-        "                {\n"
-        "                    double E_i = (arg2 - D_0)/365.0;\n"
-        "                    fResultValue2 -= E_i * arg1 / pow(r,E_i + 1.0);\n"
-        "                }\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "                double E_i = (arg2 - D_0)/365.0;\n"
+        "                fResultValue2 -= E_i * arg1 / pow(r,E_i + 1.0);\n"
         , "1" // start from 2nd element
         );
     ss << "            double fNewRate = fResultRate - fResultValue / fResultValue2;\n";
diff --git a/sc/source/core/opencl/op_logical.cxx b/sc/source/core/opencl/op_logical.cxx
index 2af37c73395e..3a3869d59175 100644
--- a/sc/source/core/opencl/op_logical.cxx
+++ b/sc/source/core/opencl/op_logical.cxx
@@ -23,7 +23,7 @@ void OpLogicalBinaryOperator::GenSlidingWindowFunction(outputstream &ss,
     GenerateFunctionDeclaration( sSymName, vSubArguments, ss );
     ss << "{\n";
     ss << "    int gid0 = get_global_id(0);\n";
-    ss << "    bool t = " << defaultOpenclValue() << ";\n";
+    ss << "    bool t = false;\n";
     for(size_t j = 0; j< vSubArguments.size(); j++)
     {
         GenerateArg( j, vSubArguments, ss );
@@ -33,6 +33,26 @@ void OpLogicalBinaryOperator::GenSlidingWindowFunction(outputstream &ss,
     ss << "}\n";
 }
 
+void OpAnd::GenSlidingWindowFunction(outputstream &ss,
+    const std::string &sSymName, SubArguments &vSubArguments)
+{
+    CHECK_PARAMETER_COUNT( 1, 30 );
+    GenerateFunctionDeclaration( sSymName, vSubArguments, ss );
+    ss << "{\n";
+    ss << "    int gid0 = get_global_id(0);\n";
+    ss << "    bool t = true;\n";
+    for(size_t j = 0; j< vSubArguments.size(); j++)
+    {
+        GenerateArg( j, vSubArguments, ss, EmptyIsNan );
+        // AND() with a svSingleVectorRef pointing to an empty cell skips that cell.
+        // See ScInterpreter::ScAnd().
+        ss << "    if( !isnan( arg" << j << " ))\n";
+        ss << "        t = t " << openclOperator() << " (arg" << j << " != 0);\n";
+    }
+    ss << "    return t;\n";
+    ss << "}\n";
+}
+
 void OpNot::GenSlidingWindowFunction(outputstream &ss,
     const std::string &sSymName, SubArguments &vSubArguments)
 {
diff --git a/sc/source/core/opencl/op_logical.hxx b/sc/source/core/opencl/op_logical.hxx
index a52d1db0cf86..15659a0625ae 100644
--- a/sc/source/core/opencl/op_logical.hxx
+++ b/sc/source/core/opencl/op_logical.hxx
@@ -22,19 +22,15 @@ class OpLogicalBinaryOperator : public Normal
     virtual bool canHandleMultiVector() const override { return true; }
     /// The C operator implementing the function.
     virtual const char* openclOperator() const = 0;
-    /// Default value when chaining the operator.
-    virtual const char* defaultOpenclValue() const = 0;
 };
 
 class OpAnd: public OpLogicalBinaryOperator
 {
 public:
+    virtual void GenSlidingWindowFunction(outputstream &ss,
+            const std::string &sSymName, SubArguments &vSubArguments) override;
     virtual std::string BinFuncName() const override { return "And"; }
     virtual const char* openclOperator() const override { return "&&"; };
-    virtual const char* defaultOpenclValue() const override { return "true"; }
-    // AND() with a svSingleVectorRef pointing to an empty cell skips that cell.
-    // See ScInterpreter::ScAnd().
-    virtual const char* rangeEmptyCellValue() const override { return "1.0"; };
 };
 
 class OpOr: public OpLogicalBinaryOperator
@@ -42,7 +38,6 @@ class OpOr: public OpLogicalBinaryOperator
 public:
     virtual std::string BinFuncName() const override { return "Or"; }
     virtual const char* openclOperator() const override { return "||"; };
-    virtual const char* defaultOpenclValue() const override { return "false"; }
 };
 
 class OpNot: public Normal
@@ -58,7 +53,6 @@ class OpXor: public OpLogicalBinaryOperator
 public:
     virtual std::string BinFuncName() const override { return "Xor"; }
     virtual const char* openclOperator() const override { return "^"; };
-    virtual const char* defaultOpenclValue() const override { return "false"; }
 };
 
 class OpIf:public Normal
diff --git a/sc/source/core/opencl/op_math.cxx b/sc/source/core/opencl/op_math.cxx
index 3ffae0d27f42..70a87261c502 100644
--- a/sc/source/core/opencl/op_math.cxx
+++ b/sc/source/core/opencl/op_math.cxx
@@ -1229,9 +1229,8 @@ void OpSumSQ::GenSlidingWindowFunction(outputstream &ss,
     ss << "{\n";
     ss << "    int gid0=get_global_id(0);\n";
     ss << "    double sum = 0.0f, arg;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            sum += pown(arg, 2);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        sum += pown(arg, 2);\n"
         );
     ss << "    return sum;\n";
     ss << "}";
@@ -1267,12 +1266,9 @@ void OpProduct::GenSlidingWindowFunction(outputstream &ss,
     ss << "    int gid0 = get_global_id(0);\n";
     ss << "    double product=1.0;\n";
     ss << "    int count = 0;\n\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "        {\n"
-        "            product = product*arg;\n"
-        "            ++count;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        product = product*arg;\n"
+        "        ++count;\n"
         );
     ss << "    if(count == 0)\n";
     ss << "        return 0;\n";
@@ -1449,13 +1445,10 @@ void OpSeriesSum::GenSlidingWindowFunction(outputstream &ss,
     ss << "    var[1] = var1;\n";
     ss << "    var[2] = var2;\n";
     ss << "    int j = 0;\n";
-    GenerateRangeArg( 3, vSubArguments, ss,
+    GenerateRangeArg( 3, vSubArguments, ss, SkipEmpty,
         "        double coeff = arg;\n"
-        "        if (!isnan(coeff))\n"
-        "        {\n"
-        "            res = res + coeff * pow(var[0], var[1] + j * var[2]);\n"
-        "            ++j;\n"
-        "        }\n"
+        "        res = res + coeff * pow(var[0], var[1] + j * var[2]);\n"
+        "        ++j;\n"
         );
     ss << "    return res;\n";
     ss << "}";
diff --git a/sc/source/core/opencl/op_statistical.cxx b/sc/source/core/opencl/op_statistical.cxx
index b2f645f1e378..68b1e447c039 100644
--- a/sc/source/core/opencl/op_statistical.cxx
+++ b/sc/source/core/opencl/op_statistical.cxx
@@ -27,17 +27,13 @@ void OpVar::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fMean = 0.0;\n";
     ss << "    double vSum = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    fMean = fSum / fCount;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            vSum += (arg - fMean) * (arg - fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg - fMean) * (arg - fMean);\n"
         );
     ss << "    if (fCount <= 1.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
@@ -58,17 +54,13 @@ void OpVarP::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double vSum = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
     ss << "    double arg = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    fMean = fSum / fCount;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            vSum += (arg - fMean) * (arg - fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg - fMean) * (arg - fMean);\n"
         );
     ss << "    if (fCount == 0.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
@@ -98,13 +90,10 @@ void OpZTest::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fSumSqr = 0.0;\n";
     ss << "    double mue = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArg( 0, vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fSumSqr += arg * arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArg( 0, vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fSumSqr += arg * arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if(fCount <= 1.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
@@ -167,14 +156,11 @@ void OpTTest::GenSlidingWindowFunction(outputstream &ss,
 
     ss << "    if(type == 1.0)\n";
     ss << "    {\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "            if (!isnan(arg1) && !isnan(arg2))\n"
-        "            {\n"
-        "                fSum1 += arg1;\n"
-        "                fSum2 += arg2;\n"
-        "                fSumSqr1 += (arg1 - arg2)*(arg1 - arg2);\n"
-        "                fCount1 += 1;\n"
-        "            }\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "            fSum1 += arg1;\n"
+        "            fSum2 += arg2;\n"
+        "            fSumSqr1 += (arg1 - arg2)*(arg1 - arg2);\n"
+        "            fCount1 += 1;\n"
         );
     ss << "        if(fCount1 < 1.0)\n";
     ss << "            return CreateDoubleError(NoValue);\n";
@@ -186,21 +172,15 @@ void OpTTest::GenSlidingWindowFunction(outputstream &ss,
     ss << "    }\n";
     ss << "    if(type == 2.0 || type == 3.0)\n";
     ss << "    {\n";
-    GenerateRangeArg( 0, vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum1 += arg;\n"
-        "            fSumSqr1 += arg * arg;\n"
-        "            fCount1 += 1;\n"
-        "        }\n"
+    GenerateRangeArg( 0, vSubArguments, ss, SkipEmpty,
+        "        fSum1 += arg;\n"
+        "        fSumSqr1 += arg * arg;\n"
+        "        fCount1 += 1;\n"
         );
-    GenerateRangeArg( 1, vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum2 += arg;\n"
-        "            fSumSqr2 += arg * arg;\n"
-        "            fCount2 += 1;\n"
-        "        }\n"
+    GenerateRangeArg( 1, vSubArguments, ss, SkipEmpty,
+        "        fSum2 += arg;\n"
+        "        fSumSqr2 += arg * arg;\n"
+        "        fCount2 += 1;\n"
         );
     ss << "        if (fCount1 < 2.0 || fCount2 < 2.0)\n";
     ss << "            return CreateDoubleError(NoValue);\n";
@@ -402,32 +382,25 @@ void OpSkew::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fMean = 0.0;\n";
     ss << "    double vSum = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if(fCount <= 2.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
     ss << "    else\n";
     ss << "        fMean = fSum / fCount;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            vSum += (arg - fMean) * (arg - fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg - fMean) * (arg - fMean);\n"
         );
     ss << "    double fStdDev = sqrt(vSum / (fCount - 1.0));\n";
     ss << "    double dx = 0.0;\n";
     ss << "    double xcube = 0.0;\n";
     ss << "    if(fStdDev == 0.0)\n";
     ss << "        return CreateDoubleError(IllegalArgument);\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            dx = (arg - fMean) / fStdDev;\n"
-        "            xcube = xcube + dx * dx * dx;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        dx = (arg - fMean) / fStdDev;\n"
+        "        xcube = xcube + dx * dx * dx;\n"
         );
     ss << "    return ((xcube * fCount) / (fCount - 1.0))";
     ss << " / (fCount - 2.0);\n";
@@ -445,32 +418,25 @@ void OpSkewp::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fMean = 0.0;\n";
     ss << "    double vSum = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if(fCount <= 2.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
     ss << "    else\n";
     ss << "        fMean = fSum / fCount;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            vSum += (arg - fMean) * (arg - fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg - fMean) * (arg - fMean);\n"
         );
     ss << "    double fStdDev = sqrt(vSum / fCount);\n";
     ss << "    double dx = 0.0;\n";
     ss << "    double xcube = 0.0;\n";
     ss << "    if(fStdDev == 0.0)\n";
     ss << "        return CreateDoubleError(IllegalArgument);\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            dx = (arg - fMean) / fStdDev;\n"
-        "            xcube = xcube + dx * dx * dx;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        dx = (arg - fMean) / fStdDev;\n"
+        "        xcube = xcube + dx * dx * dx;\n"
         );
     ss << "    return xcube / fCount;\n";
     ss << "}\n";
@@ -537,17 +503,13 @@ void OpStDev::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double vSum = 0.0;\n";
     ss << "    double fMean = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    fMean = fSum / fCount;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            vSum += (arg - fMean) * (arg - fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg - fMean) * (arg - fMean);\n"
         );
     ss << "    if (fCount <= 1.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
@@ -567,17 +529,13 @@ void OpStDevP::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double vSum = 0.0;\n";
     ss << "    double fMean = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    fMean = fSum / fCount;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if (!isnan(arg))\n"
-        "            vSum += (arg - fMean) * (arg - fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg - fMean) * (arg - fMean);\n"
         );
     ss << "    if (fCount <= 1.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
@@ -853,12 +811,9 @@ vSubArguments)
     ss << "    double tmp = 0;\n";
     ss << "    int length;\n";
     ss << "    int totallength=0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "        {\n"
-        "            nVal += (1.0 / arg);\n"
-        "            ++totallength;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        nVal += (1.0 / arg);\n"
+        "        ++totallength;\n"
         );
     ss << "    tmp = totallength/nVal;\n";
     ss << "    return tmp;\n";
@@ -1207,27 +1162,20 @@ void OpKurt:: GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fSum = 0.0;\n";
     ss << "    double vSum = 0.0;\n";
     ss << "    double totallength=0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "        {\n"
-        "            fSum += arg;\n"
-        "            totallength +=1;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        fSum += arg;\n"
+        "        totallength +=1;\n"
         );
     ss << "    double fMean = fSum / totallength;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "            vSum += (arg-fMean)*(arg-fMean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += (arg-fMean)*(arg-fMean);\n"
         );
     ss << "    double fStdDev = sqrt(vSum / (totallength - 1.0));\n";
     ss << "    double dx = 0.0;\n";
     ss << "    double xpower4 = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "        {\n"
-        "            dx = (arg -fMean) / fStdDev;\n"
-        "            xpower4 = xpower4 + (dx * dx * dx * dx);\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        dx = (arg -fMean) / fStdDev;\n"
+        "        xpower4 = xpower4 + (dx * dx * dx * dx);\n"
         );
     ss<< "    double k_d = (totallength - 2.0) * (totallength - 3.0);\n";
     ss<< "    double k_l = totallength * (totallength + 1.0) /";
@@ -1816,21 +1764,15 @@ void OpFTest::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fLength1 = 0.0;\n";
     ss << "    double fLength2 = 0.0;\n";
     ss << "    double tmp = 0;\n";
-    GenerateRangeArg( 0, vSubArguments, ss,
-        "        if( !isnan(arg))\n"
-        "        {\n"
-        "            fSum1 += arg;\n"
-        "            fSumSqr1 += arg * arg;\n"
-        "            fLength1 += 1;\n"
-        "        }\n"
+    GenerateRangeArg( 0, vSubArguments, ss, SkipEmpty,
+        "        fSum1 += arg;\n"
+        "        fSumSqr1 += arg * arg;\n"
+        "        fLength1 += 1;\n"
         );
-    GenerateRangeArg( 1, vSubArguments, ss,
-        "        if( !isnan(arg))\n"
-        "        {\n"
-        "            fSum2 += arg;\n"
-        "            fSumSqr2 += arg * arg;\n"
-        "            fLength2 += 1;\n"
-        "        }\n"
+    GenerateRangeArg( 1, vSubArguments, ss, SkipEmpty,
+        "        fSum2 += arg;\n"
+        "        fSumSqr2 += arg * arg;\n"
+        "        fLength2 += 1;\n"
         );
     ss << "    if(fLength1 < 2 || fLength2 < 2)\n"
         "        return CreateDoubleError(NoValue);\n"
@@ -2158,18 +2100,14 @@ void OpDevSq::GenSlidingWindowFunction(outputstream& ss,
     ss << "    double vSum = 0.0;\n";
     ss << "    double vMean = 0.0;\n";
     ss << "    int cnt = 0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan (arg))\n"
-        "        {\n"
-        "            vSum += arg;\n"
-        "            ++cnt;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += arg;\n"
+        "        ++cnt;\n"
         );
     ss << "    vMean = vSum / cnt;\n";
     ss << "    vSum = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan (arg))\n"
-        "            vSum += ( arg - vMean ) * ( arg - vMean );\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        vSum += ( arg - vMean ) * ( arg - vMean );\n"
         );
     ss << "    return vSum;\n";
     ss << "}\n";
@@ -2234,18 +2172,14 @@ void OpAveDev:: GenSlidingWindowFunction(outputstream &ss,
     ss << "    int gid0 = get_global_id(0);\n";
     ss << "    double sum=0.0;\n";
     ss << "    double totallength=0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "        {\n"
-        "            sum += arg;\n"
-        "            ++totallength;\n"
-        "        }\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        sum += arg;\n"
+        "        ++totallength;\n"
         );
     ss << "    double mean = sum / totallength;\n";
     ss << "    sum = 0.0;\n";
-    GenerateRangeArgs( vSubArguments, ss,
-        "        if(!isnan(arg))\n"
-        "            sum += fabs(arg-mean);\n"
+    GenerateRangeArgs( vSubArguments, ss, SkipEmpty,
+        "        sum += fabs(arg-mean);\n"
         );
     ss << "    return sum/totallength;\n";
     ss << "}";
@@ -2268,21 +2202,17 @@ void OpCovar::GenSlidingWindowFunction(outputstream& ss,
     ss << "    double fMeanY = 0.0;\n";
     ss << "    double fSumDeltaXDeltaY = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if (!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
-        "            fSumX += arg1;\n"
-        "            fSumY += arg2;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "        fSumX += arg1;\n"
+        "        fSumY += arg2;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if( fCount < 1 )\n";
     ss << "        return CreateDoubleError(NoValue);\n";
     ss << "    fMeanX = fSumX / fCount;\n";
     ss << "    fMeanY = fSumY / fCount;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if(!isnan(arg1) && !isnan(arg2))\n"
-        "            fSumDeltaXDeltaY +=(arg1-fMeanX)*(arg2-fMeanY);\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "        fSumDeltaXDeltaY +=(arg1-fMeanX)*(arg2-fMeanY);\n"
         );
     ss << "    return fSumDeltaXDeltaY / fCount;\n";
     ss << "}\n";
@@ -2305,25 +2235,19 @@ void OpForecast::GenSlidingWindowFunction(outputstream &ss,
     ss << "    double fSumSqrDeltaX = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
     GenerateArg( "arg0", 0, vSubArguments, ss );
-    GenerateRangeArgPair( 1, 2, vSubArguments, ss,
-        "        if (!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
+    GenerateRangeArgPair( 1, 2, vSubArguments, ss, SkipEmpty,
         // note that arg1 -> Y, arg2 -> X
-        "            fSumX += arg2;\n"
-        "            fSumY += arg1;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+        "        fSumX += arg2;\n"
+        "        fSumY += arg1;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if( fCount < 1 )\n";
     ss << "        return CreateDoubleError(NoValue);\n";
     ss << "    fMeanX = fSumX / fCount;\n";
     ss << "    fMeanY = fSumY / fCount;\n";
-    GenerateRangeArgPair( 1, 2, vSubArguments, ss,
-        "        if(!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
-        "            fSumDeltaXDeltaY +=(arg2-fMeanX)*(arg1-fMeanY);\n"
-        "            fSumSqrDeltaX += (arg2-fMeanX)*(arg2-fMeanX);\n"
-        "        }\n"
+    GenerateRangeArgPair( 1, 2, vSubArguments, ss, SkipEmpty,
+        "        fSumDeltaXDeltaY +=(arg2-fMeanX)*(arg1-fMeanY);\n"
+        "        fSumSqrDeltaX += (arg2-fMeanX)*(arg2-fMeanX);\n"
         );
     ss << "    if(fSumSqrDeltaX == 0.0)\n";
     ss << "        return CreateDoubleError(DivisionByZero);\n";
@@ -2347,25 +2271,19 @@ void OpInterceptSlopeBase::GenerateCode( outputstream &ss, const std::string &sS
     ss << "    double fSumDeltaXDeltaY = 0.0;\n";
     ss << "    double fSumSqrDeltaX = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if (!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
         // note that arg1 -> Y, arg2 -> X
-        "            fSumX += arg2;\n"
-        "            fSumY += arg1;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+        "        fSumX += arg2;\n"
+        "        fSumY += arg1;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if( fCount < 1 )\n";
     ss << "        return CreateDoubleError(NoValue);\n";
     ss << "    fMeanX = fSumX / fCount;\n";
     ss << "    fMeanY = fSumY / fCount;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if(!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
-        "            fSumDeltaXDeltaY +=(arg2-fMeanX)*(arg1-fMeanY);\n"
-        "            fSumSqrDeltaX += (arg2-fMeanX)*(arg2-fMeanX);\n"
-        "        }\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "        fSumDeltaXDeltaY +=(arg2-fMeanX)*(arg1-fMeanY);\n"
+        "        fSumSqrDeltaX += (arg2-fMeanX)*(arg2-fMeanX);\n"
         );
     ss << finalComputeCode;
     ss << "}\n";
@@ -2408,26 +2326,20 @@ void OpPearsonCovarBase::GenerateCode( outputstream &ss, const std::string &sSym
     ss << "    double fSumSqrDeltaX = 0.0;\n";
     ss << "    double fSumSqrDeltaY = 0.0;\n";
     ss << "    double fCount = 0.0;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if (!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
         // note that arg1 -> Y, arg2 -> X
-        "            fSumX += arg2;\n"
-        "            fSumY += arg1;\n"
-        "            fCount += 1.0;\n"
-        "        }\n"
+        "        fSumX += arg2;\n"
+        "        fSumY += arg1;\n"
+        "        fCount += 1.0;\n"
         );
     ss << "    if( fCount < " << minimalCountValue <<" )\n";
     ss << "        return CreateDoubleError(NoValue);\n";
     ss << "    fMeanX = fSumX / fCount;\n";
     ss << "    fMeanY = fSumY / fCount;\n";
-    GenerateRangeArgPair( 0, 1, vSubArguments, ss,
-        "        if(!isnan(arg1) && !isnan(arg2))\n"
-        "        {\n"
-        "            fSumDeltaXDeltaY +=(arg2-fMeanX)*(arg1-fMeanY);\n"
-        "            fSumSqrDeltaX += (arg2-fMeanX)*(arg2-fMeanX);\n"
-        "            fSumSqrDeltaY += (arg1-fMeanY)*(arg1-fMeanY);\n"
-        "        }\n"
+    GenerateRangeArgPair( 0, 1, vSubArguments, ss, SkipEmpty,
+        "        fSumDeltaXDeltaY +=(arg2-fMeanX)*(arg1-fMeanY);\n"
+        "        fSumSqrDeltaX += (arg2-fMeanX)*(arg2-fMeanX);\n"
+        "        fSumSqrDeltaY += (arg1-fMeanY)*(arg1-fMeanY);\n"
         );
     ss << finalComputeCode;
     ss << "}\n";
diff --git a/sc/source/core/opencl/opbase.cxx b/sc/source/core/opencl/opbase.cxx
index bbb9638948cd..36605ef8e977 100644
--- a/sc/source/core/opencl/opbase.cxx
+++ b/sc/source/core/opencl/opbase.cxx
@@ -181,61 +181,72 @@ VectorRefStringsToZero::VectorRefStringsToZero( const ScCalcConfig& config, cons
     forceStringsToZero = true;
 }
 
-void SlidingFunctionBase::GenerateArg( const char* name, int arg, SubArguments& vSubArguments, outputstream& ss )
+void SlidingFunctionBase::GenerateArg( const char* name, int arg, SubArguments& vSubArguments,
+    outputstream& ss, EmptyArgType empty )
 {
     assert( arg < int( vSubArguments.size()));
     FormulaToken *token = vSubArguments[arg]->GetFormulaToken();
     if( token == nullptr )
         throw Unhandled( __FILE__, __LINE__ );
-    ss << "    double " << name << ";\n";
     if(token->GetOpCode() == ocPush)
     {
         if(token->GetType() == formula::svSingleVectorRef)
         {
             const formula::SingleVectorRefToken* svr =
                 static_cast<const formula::SingleVectorRefToken *>(token);
-            ss << "    if (gid0 >= " << svr->GetArrayLength() << " || isnan(";
-            ss << vSubArguments[arg]->GenSlidingWindowDeclRef() << "))\n";
-            ss << "        " << name << " = " << rangeEmptyCellValue() << ";\n";
-            ss << "    else\n";
+            ss << "    double " << name << " = NAN;\n";
+            ss << "    if (gid0 < " << svr->GetArrayLength() << ")\n";
             ss << "        " << name << " = ";
             ss << vSubArguments[arg]->GenSlidingWindowDeclRef() << ";\n";
+            switch( empty )
+            {
+                case EmptyIsZero:
+                    ss << "    if( isnan( " << name << " ))\n";
+                    ss << "        " << name << " = 0;\n";
+                    break;
+                case EmptyIsNan:
+                    break;
+                case SkipEmpty:
+                    abort();
+                    break;
+            }
         }
         else if(token->GetType() == formula::svDouble)
-            ss << "    " << name << " = " << token->GetDouble() << ";\n";
+            ss << "    double " << name << " = " << token->GetDouble() << ";\n";
         else if(token->GetType() == formula::svString)
         {
             assert( dynamic_cast<DynamicKernelStringToZeroArgument*>(vSubArguments[arg].get()));
-            ss << "    " << name << " = 0.0;\n";
+            ss << "    double " << name << " = 0.0;\n";
         }
         else
             throw Unhandled( __FILE__, __LINE__ );
     }
     else
     {
-        ss << "    " << name << " = ";
+        ss << "    double " << name << " = ";
         ss << vSubArguments[arg]->GenSlidingWindowDeclRef() << ";\n";
     }
 }
 
-void SlidingFunctionBase::GenerateArg( int arg, SubArguments& vSubArguments, outputstream& ss )
+void SlidingFunctionBase::GenerateArg( int arg, SubArguments& vSubArguments, outputstream& ss,
+    EmptyArgType empty )
 {
     char buf[ 30 ];
     sprintf( buf, "arg%d", arg );
-    GenerateArg( buf, arg, vSubArguments, ss );
+    GenerateArg( buf, arg, vSubArguments, ss, empty );
 }
 
 void SlidingFunctionBase::GenerateArgWithDefault( const char* name, int arg, double def,
-    SubArguments& vSubArguments, outputstream& ss )
+    SubArguments& vSubArguments, outputstream& ss, EmptyArgType empty )
 {
     if( arg < int(vSubArguments.size()))
-        GenerateArg( name, arg, vSubArguments, ss );
+        GenerateArg( name, arg, vSubArguments, ss, empty );
     else
         ss << "    double " << name << " = " << def << ";\n";
 }
 
 void SlidingFunctionBase::GenerateRangeArgs( int firstArg, int lastArg, SubArguments& vSubArguments,
-    outputstream& ss, const char* code )
+    outputstream& ss, EmptyArgType empty, const char* code )
 {
     assert( firstArg >= 0 );
     assert( firstArg <= lastArg );
@@ -257,6 +268,19 @@ void SlidingFunctionBase::GenerateRangeArgs( int firstArg, int lastArg, SubArgum
                 ss << "        double arg = ";
                 ss << vSubArguments[i]->GenSlidingWindowDeclRef();
                 ss << ";\n";
+                switch( empty )
+                {
+                    case EmptyIsZero:
+                        ss << "        if( isnan( arg ))\n";
+                        ss << "            arg = 0;\n";
+                        break;
+                    case EmptyIsNan:
+                        break;
+                    case SkipEmpty:
+                        ss << "        if( isnan( arg ))\n";
+                        ss << "            continue;\n";
+                        break;
+                }
                 ss << code;
                 ss << "    }\n";
             }
@@ -268,7 +292,23 @@ void SlidingFunctionBase::GenerateRangeArgs( int firstArg, int lastArg, SubArgum
                 ss << "    {\n";
                 ss << "        double arg = ";
                 ss << vSubArguments[i]->GenSlidingWindowDeclRef() << ";\n";
-                ss << code;
+                switch( empty )
+                {
+                    case EmptyIsZero:
+                        ss << "        if( isnan( arg ))\n";
+                        ss << "            arg = 0;\n";
+                        ss << code;
+                        break;
+                    case EmptyIsNan:
+                        ss << code;
+                        break;
+                    case SkipEmpty:
+                        ss << "        if( !isnan( arg ))\n";
+                        ss << "        {\n";
+                        ss << code;
+                        ss << "        }\n";
+                        break;
+                }
                 ss << "    }\n";
             }
             else if(token->GetType() == formula::svDouble)
@@ -301,19 +341,19 @@ void SlidingFunctionBase::GenerateRangeArgs( int firstArg, int lastArg, SubArgum
 }
 
 void SlidingFunctionBase::GenerateRangeArgs( SubArguments& vSubArguments,
-    outputstream& ss, const char* code )
+    outputstream& ss, EmptyArgType empty, const char* code )
 {
-    GenerateRangeArgs( 0, vSubArguments.size() - 1, vSubArguments, ss, code );
+    GenerateRangeArgs( 0, vSubArguments.size() - 1, vSubArguments, ss, empty, code );
 }
 
 void SlidingFunctionBase::GenerateRangeArg( int arg, SubArguments& vSubArguments,
-    outputstream& ss, const char* code )
+    outputstream& ss, EmptyArgType empty, const char* code )
 {
-    GenerateRangeArgs( arg, arg, vSubArguments, ss, code );
+    GenerateRangeArgs( arg, arg, vSubArguments, ss, empty, code );
 }
 
 void SlidingFunctionBase::GenerateRangeArgPair( int arg1, int arg2, SubArguments& vSubArguments,
-    outputstream& ss, const char* code, const char* firstElementDiff )
+    outputstream& ss, EmptyArgType empty, const char* code, const char* firstElementDiff )
 {
     assert( arg1 >= 0 && arg1 < int (vSubArguments.size()));
     assert( arg2 >= 0 && arg2 < int (vSubArguments.size()));
@@ -345,19 +385,50 @@ void SlidingFunctionBase::GenerateRangeArgPair( int arg1, int arg2, SubArguments
         throw Unhandled( __FILE__, __LINE__ );
     }
 
-    GenerateDoubleVectorLoopHeader( ss,
-        pDVR1->GetArrayLength() < pDVR2->GetArrayLength() ? pDVR1 : pDVR2,
-        firstElementDiff );
+    // If either of the ranges ends with empty cells, it will not include those last
+    // nan values (its GetArrayLength() will be less than its GetRefRowSize().
+    // If we skip empty cells, just iterate until both ranges have elements, but if
+    // we need to iterate even over empty cells, so use the longer one.
+    // FIXME: If both ranges end with empty cells, this does not actually iterate
+    // over all empty cells.
+    const formula::DoubleVectorRefToken* loopDVR;
+    bool checkBounds;
+    if( empty == SkipEmpty )
+    {
+        loopDVR = pDVR1->GetArrayLength() < pDVR2->GetArrayLength() ? pDVR1 : pDVR2;
+        checkBounds = false;
+    }
+    else
+    {
+        loopDVR = pDVR1->GetArrayLength() > pDVR2->GetArrayLength() ? pDVR1 : pDVR2;
+        checkBounds = true;
+    }
+    GenerateDoubleVectorLoopHeader( ss, loopDVR, firstElementDiff );
     ss << "        double arg1 = ";
-    ss << vSubArguments[arg1]->GenSlidingWindowDeclRef(true) << ";\n";
+    ss << vSubArguments[arg1]->GenSlidingWindowDeclRef(!checkBounds) << ";\n";
     ss << "        double arg2 = ";
-    ss << vSubArguments[arg2]->GenSlidingWindowDeclRef(true) << ";\n";
+    ss << vSubArguments[arg2]->GenSlidingWindowDeclRef(!checkBounds) << ";\n";
+    switch( empty )
+    {
+        case EmptyIsZero:
+            ss << "        if( isnan( arg1 ))\n";
+            ss << "            arg1 = 0;\n";
+            ss << "        if( isnan( arg2 ))\n";
+            ss << "            arg2 = 0;\n";
+            break;
+        case EmptyIsNan:
+            break;
+        case SkipEmpty:
+            ss << "        if( isnan( arg1 ) || isnan( arg2 ))\n";
+            ss << "            continue;\n";
+            break;
+    }
     ss << code;
     ss << "    }\n";
 }
 
 void SlidingFunctionBase::GenerateRangeArgElement( const char* name, int arg, const char* element,
-    SubArguments& vSubArguments, outputstream& ss )
+    SubArguments& vSubArguments, outputstream& ss, EmptyArgType empty )
 {
     assert( arg >= 0 && arg < int (vSubArguments.size()));
     FormulaToken *token = vSubArguments[arg]->GetFormulaToken();
@@ -367,7 +438,7 @@ void SlidingFunctionBase::GenerateRangeArgElement( const char* name, int arg, co
         throw Unhandled( __FILE__, __LINE__ );
     const formula::DoubleVectorRefToken* pDVR =
         static_cast<const formula::DoubleVectorRefToken *>(token);
-    ss << "    double " << name << " = " << rangeEmptyCellValue() << ";\n";
+    ss << "    double " << name << " = NAN;\n";
     ss << "    {\n";
     // GenSlidingWindowDeclRef() may refer to 'i' variable.
     ss << "        int i = 0;\n";
@@ -377,6 +448,18 @@ void SlidingFunctionBase::GenerateRangeArgElement( const char* name, int arg, co
     ss << element << " < " << pDVR->GetArrayLength() << " )\n";
     ss << "            " << name << " = " << vSubArguments[arg]->GenSlidingWindowDeclRef(true) << ";\n";
     ss << "    }\n";
+    switch( empty )
+    {
+        case EmptyIsZero:
+            ss << "        if( isnan( " << name << " ))\n";
+            ss << "            " << name << " = 0;\n";
+            break;
+        case EmptyIsNan:
+            break;
+        case SkipEmpty:
+            abort();
+            break;
+    }
 }
 
 void SlidingFunctionBase::GenerateDoubleVectorLoopHeader( outputstream& ss,
diff --git a/sc/source/core/opencl/opbase.hxx b/sc/source/core/opencl/opbase.hxx
index c274eeaf2024..8e1a167258cf 100644
--- a/sc/source/core/opencl/opbase.hxx
+++ b/sc/source/core/opencl/opbase.hxx
@@ -341,45 +341,53 @@ public:
     virtual void GenSlidingWindowFunction( outputstream&,
         const std::string&, SubArguments& ) = 0;
 protected:
-    // generate code for "double <name> = <value>;" from vSubArguments, svDoubleVectorRef is not supported
-    void GenerateArg( const char* name, int arg, SubArguments& vSubArguments, outputstream& ss );
+    // This enum controls how the generated code will handle empty cells in ranges.
+    enum EmptyArgType
+    {
+        EmptyIsZero, // empty cells become 0.0
+        EmptyIsNan,  // empty cells become NAN, use isnan() to check in code
+        SkipEmpty    // empty cells will be skipped
+    };
+    void GenerateFunctionDeclaration( const std::string& sSymName,
+        SubArguments& vSubArguments, outputstream& ss );
+    // Generate code for "double <name> = <value>;" from vSubArguments, svDoubleVectorRef is not supported.
+    static void GenerateArg( const char* name, int arg, SubArguments& vSubArguments, outputstream& ss,
+        EmptyArgType empty = EmptyIsZero );
     // overload, variable will be named "arg<arg>"
-    void GenerateArg( int arg, SubArguments& vSubArguments, outputstream& ss );
+    static void GenerateArg( int arg, SubArguments& vSubArguments, outputstream& ss,
+        EmptyArgType empty = EmptyIsZero );
     // generate code for "double <name> = <value>;" from vSubArguments, if it exists,
     // otherwise set to <def>
-    void GenerateArgWithDefault( const char* name, int arg, double def, SubArguments& vSubArguments,
-        outputstream& ss );
-    void GenerateFunctionDeclaration( const std::string& sSymName,
-        SubArguments& vSubArguments, outputstream& ss );
+    static void GenerateArgWithDefault( const char* name, int arg, double def, SubArguments& vSubArguments,
+        outputstream& ss, EmptyArgType empty = EmptyIsZero );
     // Generate code that will handle all arguments firstArg-lastArg (zero-based, inclusive),
     // including range arguments (svDoubleVectorRef) and each value will be processed by 'code',
-    // value will be named "arg". There is no isnan(arg) checking.
+    // value will be named "arg".
     static void GenerateRangeArgs( int firstArg, int lastArg, SubArguments& vSubArguments,
-        outputstream& ss, const char* code );
+        outputstream& ss, EmptyArgType empty, const char* code );
     // overload, handle all arguments
-    static void GenerateRangeArgs( SubArguments& vSubArguments, outputstream& ss, const char* code );
+    static void GenerateRangeArgs( SubArguments& vSubArguments, outputstream& ss,
+        EmptyArgType empty, const char* code );
     // overload, handle the given argument
-    static void GenerateRangeArg( int arg, SubArguments& vSubArguments, outputstream& ss, const char* code );
+    static void GenerateRangeArg( int arg, SubArguments& vSubArguments, outputstream& ss,
+        EmptyArgType empty, const char* code );
     // Overload.
     // Both arguments must be svDoubleRef of the same size.
     // If 'firstElementDiff' is set, the loop start will be offset by '+ firstElementDiff'.
     void GenerateRangeArg( int arg1, int arg2, SubArguments& vSubArguments,
-        outputstream& ss, const char* code, const char* firstElementDiff = nullptr );
+        outputstream& ss, EmptyArgType empty, const char* code, const char* firstElementDiff = nullptr );
     // Generate code that will handle the given two arguments in one loop where n-th element of arg1 and arg2
     // will be handled at the same time, named 'arg1' and 'arg2'.
     // Both arguments must be svDoubleRef of the same size.
     // If 'firstElementDiff' is set, the loop start will be offset by '+ firstElementDiff'.
     static void GenerateRangeArgPair( int arg1, int arg2, SubArguments& vSubArguments,
-        outputstream& ss, const char* code, const char* firstElementDiff = nullptr );
+        outputstream& ss, EmptyArgType empty, const char* code, const char* firstElementDiff = nullptr );
     // Generate code for "double <name> = range[<element>]" from vSubArguments.
     // The argument must be svDoubleRef.
-    // There is no isnan(arg) checking.
-    void GenerateRangeArgElement( const char* name, int arg, const char* element,
-        SubArguments& vSubArguments, outputstream& ss );
+    static void GenerateRangeArgElement( const char* name, int arg, const char* element,
+        SubArguments& vSubArguments, outputstream& ss, EmptyArgType empty );
     static void GenerateDoubleVectorLoopHeader( outputstream& ss,
         const formula::DoubleVectorRefToken* pDVR, const char* firstElementDiff );
-    // The default value when there is an empty cell in a range (or it's out of bounds).
-    virtual const char* rangeEmptyCellValue() const { return "0.0"; };
 };
 
 class Normal : public SlidingFunctionBase