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

[Naresh Kumar Saini]

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

[macropod]

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

Post by Naresh Kumar Saini
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.
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

[Naresh Kumar Saini]

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

Post by macropod
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]
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party

Post by Naresh Kumar Saini
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.
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

.

[macropod]

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]

Post by Naresh Kumar Saini
Macropod,
It happens in (perhaps) all versions -- I have checked Ver 97, 2000, 2003
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

Post by macropod
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]
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party

Post by Naresh Kumar Saini
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.
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

.

[Naresh Kumar Saini]

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

Post by macropod
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]

Post by Naresh Kumar Saini
Macropod,
It happens in (perhaps) all versions -- I have checked Ver 97, 2000, 2003
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

Post by macropod
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]
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party

Post by Naresh Kumar Saini
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.
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

.

.

[f***@gmail.com]

Post by Naresh Kumar Saini
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.
(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

Post by macropod
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]

Post by Naresh Kumar Saini
Macropod,
It happens in (perhaps) all versions -- I have checked Ver 97, 2000, 2003
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

Post by macropod
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]
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party

Post by Naresh Kumar Saini
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.
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.