• Table Formula =SUM(ABOVE) give incorrect result when there is

    From funshooters@gmail.com@21:1/5 to Naresh Kumar Saini on Mon Feb 10 15:00:28 2020
    On Thursday, December 10, 2009 at 6:43:01 PM UTC+11, Naresh Kumar Saini wrote:
    Macropod,

    I did some more tests on the line you suggested and I noticed something which may help identify the exact cause of error in the formula result.

    (1) This behaviour (incorrect =sum(above) formula result) ALWAYS occur whenever there is only one row to sum in ANY part. This means, the error would occur in Part-A, Part-B or Part-C, etc if there is only one row to sum in this Part. As soon as there are two or more rows to sum, the formula start
    showing correct result.

    (2) This behaviour can be reproduced in following steps:

    (a) 2x2 Table: (second row is merged)
    1 2
    2 [formula is =sum(above), result is 2]

    (b) 2x3 table: (third row is merged)
    _ _ [first col. may have number, second col. must be blank]
    1 2
    3 [formula is =sum(above), result is 3]

    (c) 2x3 table: (third row is merged)
    1 2 [first col. may be blank, second col. must have zero/number]
    3 4
    6 [formula is =sum(above), result is 6]

    (3) Please note that =sum(above) sum the last column merged, i.e. if A, B, C, D are merged, it will sum column D only.

    (4) Please also note that in 2(b) above, where FIRST row is blank, =sum(above) has summed column A & B of SECOND row.

    It seems this error occur because of a clash between how Word interpret “above” in merged cells of a table and formula =sum(above) try to sum consecutive numeric cells above itself – but when it do not find more than one consecutive numeric cells above it (and it is in a merged cell), it tend to sum the columns above the merged cell.

    N.K.Saini


    "macropod" wrote:

    Hi N.K.Saini,

    With the table structure you supplied, I would expect the second sub-total to display 315 (which is what I get), not 243, since 315
    is the value of the preceding row. Does the Part-B merged row have any numbers in it? If so, it is likely that Word is including the
    sum of those numbers (-72) in the second sub-total.

    To avoid any numbers in the Part-B merged row affecting the subsequent calculations I'd suggest putting that cell's contents into
    another single-celled table nested on that row. With a bit of care in formatting, no one will notice the nested table's presence.


    --
    Cheers
    macropod
    [Microsoft MVP - Word]


    "Naresh Kumar Saini" <NareshKumarSaini@discussions.microsoft.com> wrote in message
    news:D79A16B8-BEDD-452B-A26E-9D943C726E21@microsoft.com...
    Macropod,

    It happens in (perhaps) all versions -- I have checked Ver 97, 2000, 2003 and 2007. The table have many rows, say 30, or more. For example:

    Sl_No Qty Rate Amount <-- Table header row.
    Part-A <-- Merged cells in this row, i.e. A,B,C are merged.
    (1) 11 15 165
    (2) 13 18 234
    SubTotal 399 <-- SubTotal is correct: there are MORE THAN ONE line to total.
    Part-B < -- Merged cells in this row, i.e. A,B,C are merged.
    (1) 15 21 315
    SubTotal 243 <-- SubTotal is incorrect: there is ONLY ONE line to total. NetTotal 714 <-- NetTotal is correct irrespective of incorrect SubTotals.

    SubTotal is calculated by =SUM(ABOVE). This row also has cells A,B,C merged.
    The result is displayed in cell D, which is visually below cell D of preceeding rows. This formula sums CONSECUTIVE non-blank numbers in cells ABOVE. Due to merged (as well as blank) cell on Part-X rows, the SubTotal formulas always calculate total for a given Part-X only, till it encounters a
    blank cell.

    NetTotal is calculated by =SUM(D:D). It can calculate the NetTotal correctly
    because its result is not disturbed by Part-X rows and SubTotal rows (which
    do not have a cell D due to merging) and which are anyway blank.

    This template always work correctly for me except in cases when there is only one row to sum in a given Part-X by chance. Strange...

    I can attach a copy of this template, but it seems there is no option to attach files. I can also send a copy by email.

    N.K.Saini


    "macropod" wrote:

    Hi N.K.Saini,

    Which Word version? Do you mean the formula fails if the table has only two rows? If not how many rows does the table have and on
    which row(s) are the data?

    --
    Cheers
    macropod
    [Microsoft MVP - Word]
    PS: See my discussion on SUM(ABOVE) etc, in my Word Field Maths Tutorial, at:
    http://lounge.windowssecrets.com/index.php?showtopic=365442
    or
    http://www.gmayor.com/downloads.htm#Third_party



    "Naresh Kumar Saini" <NareshKumarSaini@discussions.microsoft.com> wrote in message
    news:9C331385-75FB-4E42-9846-709C181C8389@microsoft.com...
    I am using a Word Template for preparing invoices for quite some time. This
    Template is serving my purpose better than an Excel Template because of
    better word processing capabilities of Word along with basic calculations
    support in tables.

    However, in my Template a formula [=SUM(ABOVE)] in a table give obviously
    incorrect result when there is only one row above. I can attach a copy of
    this Template if it is allowed by this Discussion Group, or send it by email.
    Meanwhile I try to explain the situation below:

    This invoice would often have three or more Parts, e.g. Part-A, Part-B, …
    Below each Part there is a row for Sub Total Part-X, and in this the Sub
    Total is calculated with =SUM(ABOVE). But in cases when there is only one row
    in a given Part, the Sub Total Part-X [i.e. =SUM(ABOVE)] give obviously
    incorrect result. The result is calculated correctly as soon as I insert one
    or more rows in this Part.

    Luckily, the final Total is not affected by such incorrect =SUM(ABOVE) >> > formula result because it use a formula =SUM(F:F). This work because in all
    the row of Sub Total Part-X, the Sub Total is only visually displayed below
    column F but due to merged cells it is not actually in column F. =SUM(ABOVE)
    sums all consecutive rows visually above it but =SUM(F:F) sum all numbers in
    column F.

    So I have to manually type in the Sub Total Part-X where there is only one
    row in a given Part-X. Am I doing something wrong or is there a work around?
    A can attach a copy of this Template if it is allowed.

    N.K.Saini

    .


    .


    I have the same thing happening to me in Word in Office 365, if my table only has one row of values and I sum the Total column, the sum(above) formula doesn't just look at the value immediately above the formula in the Total column and add vertically,
    instead it adds all of the values in the row above it horizontally, so if I have a Qty column, a Unit Price column and a Total Price column it adds these 3 values together instead of just looking at the value in the Total Column. So if my row says Qty 1,
    Unit Price $95,700.00, Total Price $95,700.00 the result of the =sum(above) formula in the cell immediately below the Total Price $95,700.00 yields a result of 1 + $95,700.00 + $95,700.00 instead of just $95,700.00. I would have thought that Microsoft by
    now would have been a whole lot smarter than this.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)