Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.word.tables




Table Formula =SUM(ABOVE) give incorrect result when there is only
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. PartA, PartB, €¦ Below each Part there is a row for Sub Total PartX, 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 PartX [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 PartX, 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 PartX where there is only one row in a given PartX. 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
Posted to microsoft.public.word.tables




Table Formula =SUM(ABOVE) give incorrect result when there is only
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. PartA, PartB, €¦ Below each Part there is a row for Sub Total PartX, 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 PartX [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 PartX, 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 PartX where there is only one row in a given PartX. 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
Posted to microsoft.public.word.tables




Table Formula =SUM(ABOVE) give incorrect result when there is
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. PartA  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. PartB  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 nonblank numbers in cells ABOVE. Due to merged (as well as blank) cell on PartX rows, the SubTotal formulas always calculate total for a given PartX 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 PartX 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 PartX 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. PartA, PartB, €¦ Below each Part there is a row for Sub Total PartX, 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 PartX [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 PartX, 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 PartX where there is only one row in a given PartX. 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 . 
#4
Posted to microsoft.public.word.tables




Table Formula =SUM(ABOVE) give incorrect result when there is
Hi N.K.Saini,
With the table structure you supplied, I would expect the second subtotal to display 315 (which is what I get), not 243, since 315 is the value of the preceding row. Does the PartB 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 subtotal. To avoid any numbers in the PartB merged row affecting the subsequent calculations I'd suggest putting that cell's contents into another singlecelled 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" wrote in message news 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. PartA  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. PartB  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 nonblank numbers in cells ABOVE. Due to merged (as well as blank) cell on PartX rows, the SubTotal formulas always calculate total for a given PartX 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 PartX 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 PartX 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. PartA, PartB, €¦ Below each Part there is a row for Sub Total PartX, 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 PartX [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 PartX, 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 PartX where there is only one row in a given PartX. 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 . 
#5
Posted to microsoft.public.word.tables




Table Formula =SUM(ABOVE) give incorrect result when there is
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 PartA, PartB or PartC, 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 subtotal to display 315 (which is what I get), not 243, since 315 is the value of the preceding row. Does the PartB 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 subtotal. To avoid any numbers in the PartB merged row affecting the subsequent calculations I'd suggest putting that cell's contents into another singlecelled 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" wrote in message news 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. PartA  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. PartB  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 nonblank numbers in cells ABOVE. Due to merged (as well as blank) cell on PartX rows, the SubTotal formulas always calculate total for a given PartX 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 PartX 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 PartX 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. PartA, PartB, €¦ Below each Part there is a row for Sub Total PartX, 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 PartX [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 PartX, 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 PartX where there is only one row in a given PartX. 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 . . 
Reply 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Forum  
=SUM(ABOVE) give incorrect result when there is only one row above  Tables  
=SUM(ABOVE) give incorrect result when there is only one row above  Tables  
Date form field incorrect result  Microsoft Word Help  
Incorrect result from =count(above)  Tables  
How do you create a formula in a table where the result appears a.  Tables 