Results 1 to 6 of 6
Thread: Tables and sum above (2003)

20050324, 15:07 #1
 Join Date
 May 2002
 Location
 London, Gtr London, England
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Tables and sum above (2003)
I have a table in Word 2003 with two rows. Row one has 12 cells and Row two has 5 cells. The last three cells of Row one line up with the last three cells in Row 2. I have an amount in Row One, Cell 12 which I would like to sum in Row two cell 5. I put in Sum Above in Row Two cell 5 but it not only sums above but also sums any other numbers in Row one from cell 5 onwards. For example
Row One Cell 5 contains a date  24Mar05
Row One Cell 7 contains text and a number  Staff: 1
Row One Cell 12 contains an amount  100.00
Sum above in Row Two cell 5 adds 24 + 05 + 1 + 100 which equals 130!
It seems to work in Word 2000 ok, but in 2003 it don't like it. Any ideas anyone?
Thanks

20050324, 16:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Tables and sum above (2003)
Why do you need a SUM formula if you want to refer to the amount in a single cell? I don't know why SUM doesn't work correctly, but try the following:
 Remove the formula from the cell in row 2, column 5.
 Press Ctrl+F9 (in this cell) to insert field brackets { } (do not type the brackets yourself).
 Type =R1C12 between the brackets (or =L1).
 Press F9 to hide the field code and update the value.

20050325, 07:59 #3
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,120
 Thanks
 2
 Thanked 439 Times in 362 Posts
Re: Tables and sum above (2003)
Hi Lynn,
Word's SUM(ABOVE) formula works on values that are in the same column of the table. The mere fact that two cells are aligned vertically doesn't mean that Word's SUM(ABOVE) formula will view them as being in the same column, since column positioning is counted from the left side of the table. Therefore, a SUM(ABOVE) formula in column 5 will add up all column 5 values, subject to the limitations explained in <post#=365442>post 365442</post#>. As you've discovered, Word even tries to interpret dates as numbers when a referenced cell contains a date, with weird results. This behaviour apparently afflicts all Word versions, so I'm surprised you found a difference.
As for the 130 result in row 2, it sounds like row 1 has multiple values in the same cell as the date, perhaps separated by tabs, rather than the values being in separate columns. When more than one number appears in a cell, Word's table formulae add them all together.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20050328, 16:20 #4
 Join Date
 May 2002
 Location
 London, Gtr London, England
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Tables and sum above (2003)
Thanks for this. It is weird that there is a difference between 2000 and 2003. The template I have created in 2000 is an expenses template, the table with the figures and dates in it, gets row after row added, when the user clicks on a finished button, the two tables get joined together and then the cell 5 of the row with 5 cells in it uses the sum above to calculate the total amount. It all works well in 2000 and has done so for the last three years! we are soon to move to 2003 and while one of our users on the pilot tried to create an expense doc using this template, thats when we discovered that the sum above does not actually some above. Thanks anyway  looks like I am going to have to rewrite this template.

20050328, 16:22 #5
 Join Date
 May 2002
 Location
 London, Gtr London, England
 Posts
 14
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Tables and sum above (2003)
Thanks for this  will give it a try. The reason I need a sum formula if I refer to the amount in a single cell, is that the template is an Expenses template, so the user may only have one expense entry, and the Total cell will still need to calculate for the accounts department.
Lynn

20050329, 02:53 #6
 Join Date
 May 2003
 Posts
 69
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Tables and sum above (2003)
Hi Lynn
I had better success with my expense forms when I changed my word table to an embedded excel table in the word template. Users were comfortable with the table structure and excel handled the calculations well. Accounting was thrilled because they could just grab the sheet and run their weekly reports from it. Another option is to use a protected form with calculations on exit. Both have worked well for me.
Good luck!