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
). 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/ind...owtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party
"Naresh Kumar Saini" wrote in message
...
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
.