Error in setting formula - Maximum limit for formula

Posted by: swatibhoge on 8 September 2017, 2:37 pm EST

  • Posted 8 September 2017, 2:37 pm EST

    <font face="arial,helvetica,sans-serif" size="2">Hello,</font>

    <font face="arial,helvetica,sans-serif" size="2"> We are using Farpoint Spread for .NET windows - version v4.0.3512.</font>

    <font face="arial,helvetica,sans-serif" size="2">We are getting error in setting formula in a cell. We are populating around <span style="font-size:12pt;font-family:'Times New Roman';">42000 rows and need to display sum of every 10th row in the top most row.</span></font>

    <font face="arial,helvetica,sans-serif"><span style="font-size:12pt;font-family:'Times New Roman';">$C$28+$C$38+$C$48+$C$58+$C$68+$C$78 ...... till </span><span style="font-size:12pt;font-family:'Times New Roman';">$C$41758</span></font><font face="arial,helvetica,sans-serif" size="2"><span style="font-size:12pt;font-family:'Times New Roman';"></span></font>

    <font face="arial,helvetica,sans-serif" size="2"><span style="font-size:12pt;font-family:'Times New Roman';">That is around 4173 expressions in a formula. </span></font><font face="arial,helvetica,sans-serif">In this case formula becomes too large and hence we get overflow error in Farpoint.Win.Spread.dll  
    </font>

    <font face="arial,helvetica,sans-serif" size="2">We would like to know the maximum limit of expressions/characters that can be set in a formula and if there is any work around for this scenario.</font>

    <font face="arial,helvetica,sans-serif"> Please suggest.</font>

    <font face="arial,helvetica,sans-serif"> </font>

    <font face="arial,helvetica,sans-serif">Thanks & Regards,</font>

    <font face="arial,helvetica,sans-serif">Swati Bhoge.
    </font>

    <font face="arial,helvetica,sans-serif">
    </font>

  • Replied 8 September 2017, 2:37 pm EST

    The size of your formula is eating up alot of your system memory and no doubt causing problems. I think there is a better way try using the SUBTOTAL function


    <H4>Summary</H4>

    This function calculates a subtotal of a list of numbers using a specified built-in function.


    <H4>Syntax</H4>

    SUBTOTAL(<I>functioncode</I>,<I>value1</I>,<I>value2</I>,...)


    SUBTOTAL(<I>functioncode</I>,<I>array</I>)


    <H4>Arguments</H4>

    The <I>functioncode</I> argument is the number that represents the built-in function to use for the subtotal, as given in this table.



    <TABLE class=list>

    <TR>
    <TH>Built-In Function      </TH>
    <TH>Function Code </TH></TR>
    <TR>
    <TD>AVERAGE</TD>
    <TD>1</TD></TR>
    <TR>
    <TD>COUNT</TD>
    <TD>2</TD></TR>
    <TR>
    <TD>COUNTA</TD>
    <TD>3</TD></TR>
    <TR>
    <TD>MAX</TD>
    <TD>4</TD></TR>
    <TR>
    <TD>MIN</TD>
    <TD>5</TD></TR>
    <TR>
    <TD>PRODUCT</TD>
    <TD>6</TD></TR>
    <TR>
    <TD>STDEV</TD>
    <TD>7</TD></TR>
    <TR>
    <TD>STDEVP</TD>
    <TD>8</TD></TR>
    <TR>
    <TD>SUM</TD>
    <TD>9</TD></TR>
    <TR>
    <TD>VAR</TD>
    <TD>10</TD></TR>
    <TR>
    <TD>VARP</TD>
    <TD>11</TD></TR></TABLE>


     


    Each additional argument can be a double-precision floating-point value, an integer value, or an array (cell range) of these. Up to 255 arguments can be included. You can use a single array (cell range) instead of a list of values. You can use multiple arrays (cell ranges) as well.


    <H4>Remarks</H4>

    The SUBTOTAL function does not include other SUBTOTAL formula results that are in the same range.


    <H4>Data Types</H4>

    Accepts numeric data for all arguments. Returns numeric data.


    <H4>Examples</H4>

    SUBTOTAL(8,A1:B7)


    <H4>Version Available</H4>

    This function is available in product version 2.0 or later.

  • Replied 8 September 2017, 2:37 pm EST

    Hello,

    Thank you for the quick reply.

    We tried using SUBTOTAL function and getting error

    "Invalid number of parameters were passed to function. Error offset: 28125".

    Since we need sum of every 10th row, we can not provide range in this formula and had to provide each cell reference like

    <font size="2">SUBTOTAL(9, <span style="font-size:12pt;font-family:'Times New Roman';">C28, C38, C48, ......</span><span style="font-size:12pt;font-family:'Times New Roman';">, C41758) which is again generating an error.</span></font>

    <font size="2"><span style="font-size:12pt;font-family:'Times New Roman';"></span></font>

    We guess problem is in number of expressions OR the limit for Formula.

    Kindly check and suggest.

     

    Thanks & Regards,

    Swati Bhoge.

     

  • Replied 8 September 2017, 2:37 pm EST

    Hello,

    Yes you are right that formulas
    have a limit i.e. they can take at most 255 values where value may be
    a single value or a range of cells. For your application which having approx. 42000 rows wherein you want to sum every 10th row, hence your application would sum
    up 4200 values approx. So, you can use SUBTOTAL function as explained below:-



    <blockquote>SUM(SUBTOTAL(9,$C$28,$C$38,$C$48),SUBTOTAL(9,$C$58,$C$68,$C$78), ......)
    </blockquote>
    Since
    each SUBTOTAL function can take 255 values and then SUM function can again take
    255 values, so you can simply divide the number of values in the
    subtotal function and then have their final sum using SUM function.


    Yet another possible solution would be a logical implementation wherein we are adding up every 10th cell in particular Spread Column:-
    <blockquote>        private void button1_Click(object sender, EventArgs e)
            {
                int sum = 0;
                for (int i = 9; i < fpSpread1.ActiveSheet.RowCount; i = i + 10)
                {
                    if (fpSpread1.ActiveSheet.Cells[i, 0].Value != null)
                    {
                        sum += (int)fpSpread1.ActiveSheet.Cells[i, 0].Value;
                    }
                }
                fpSpread1.ActiveSheet.Cells[0, 0].Value = sum;
            }
    </blockquote>


    It is simply taking each value and adding it up to the sum and finally displaying it in the cell you require.


    Hope this will help you.Thanks.

  • Replied 8 September 2017, 2:37 pm EST

    SwatiBhoge,


    Individual functions (e.g. SUM, SUBTOTAL) are limited to 255 parameters.  This is the same limitation as Excel.  In the case of the SUM function, you could work around this limitation by using a sum of sums.  For example, if you needed to sum 10000 values then you could apply the SUM function to blocks of 100 values and then apply the SUM function to those sums.


        =SUM(SUM(A1,A2,...,A100),SUM(A101,A102,...,A200),...,SUM(A9901,A9902,...,A10000))


    There is no fixed limit on the length of the text representation of a formula or the number of elements in a formula.  However, formulas are ultimately limited by amount of available memory and/or amount of available stack.  For example, we convert the external text representation of the formula into an internal parsed expression tree.  We then use recursion to scan the tree while building dependency links for the formula and while evaluating the formula.  If the tree is extremely deep then a stack overflow exception can occur during the recursive scan of the tree.  In your original example...


        =$C$28+$C$38+...+$C$41758


    the formula was resulting in parsed expression tree that was over 4000 levels deep.  You could work around the stack limitation by doing an addition of additions.  For example, you could add up blocks of 100 values and then add up those results.  This would reduce the parsed expression tree from about 4000 levels deep to about 140 levels deep.


        =($C$28+...+$C$1018)+($C$1028+...+$C$2018)+...+($C$41028...+$C$41758)


    Note that Excel has much more restrictive limitations on formula size.  Excel limits text representation of formulas to 8192 characters.  Your original formula was over 36000 characters.

  • Replied 8 September 2017, 2:37 pm EST

    Thanks a lot, Bobbyo and Reeva,

    We will try with adding sub formulae within a formula.

    FYI, The logical solution provided (by running loop) will not be suitable in our case as the cells are editable.

     

    Thanks & Regards,

    Swati Bhoge.

Need extra support?

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

Learn More

Forum Channels