A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Tables
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

=SUM(ABOVE) give incorrect result when there is only one row above



 
 
Thread Tools Display Modes
  #1  
Old October 15th, 2009, 04:25 PM posted to microsoft.public.word.tables
Naresh Kumar Saini
external usenet poster
 
Posts: 12
Default =SUM(ABOVE) give incorrect result when there is only one row above

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
  #2  
Old October 15th, 2009, 10:21 PM posted to microsoft.public.word.tables
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default =SUM(ABOVE) give incorrect result when there is only one row above

The formula { =SUM(ABOVE) } works fine here no matter how many rows there
are above the row in which that formula appears except in the case where a
row other than the first or last row in the range to be summed is blank.

That is the following work

Blank
Number
Blank
Result = Number

or

Blank
Number1
Number2
Blank
Result = Number1 + Number2

but

Number1
Blank
Number2
Result = Number2
--
Hope this helps

Doug Robbins - Word MVP
Please reply only to the newsgroups unless you wish to avail yourself of my
services on a paid, professional basis.

"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



  #3  
Old October 15th, 2009, 11:49 PM posted to microsoft.public.word.tables
macropod[_2_]
external usenet poster
 
Posts: 2,402
Default =SUM(ABOVE) give incorrect result when there is only one row above

Hi Naresh Kumar Saini,

For an explanation of the limitations of SUM(ABOVE), plus info on how to do a wide range of calculations in Word, check out my Word
Field Maths Tutorial, at:
http://www.wopr.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party


--
Cheers
macropod
[Microsoft MVP - Word]


"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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.