formula in tables

D

dsr

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I create extensive tables in Word and use the formula function to add columns for totals. This worked great on the PC but on the Mac there seems to be a point at which the formula "deactivates" and all that shows in the cell is the equal sign. I can type in the formula but the result it gives me is wrong. I cannot find the pattern in this. I thought at first it was an issue with converting a PC file to a Mac file but I encounter the same problem in files I create fresh on the Mac (.docx) as well. I need this functionality for my work ... anyone know what's happening or have any solutions?
 
D

dsr

Thanks, your tutorial is really great and covers all the struggles I stumbled through when figuring out how to work with tables ... would that I'd known of it back then!

However, the problem I'm encountering is that =SUM(ABOVE) in tables which worked fine in Word 2000 on the PC do not work when resaved in Word for Mac 2008, whether saved in Word 97-2004 format or in Word .docx format. Nor in new tables that I create. The formula gives the appearance of working but the sum is incorrect. I know I've hit the cell where he problem will start because instead of presenting the formula =SUM(ABOVE) as for the preceeding cells, it simply presents the =. From that point on, the formula does not work correctly although it does present a sum result. I thought it might be some sort of limit on the count of items the formula will sum in Word for Mac 2008; in three of the tables, the problem point is occurs at row 85 and in one table at row 84...close but not exact.

In the row 84 table, which I converted from PC to Mac, I detected a pattern in which the =SUM(ABOVE) began to SUBTRACT the numbers, in order, from the start of the column! So, for example, in row 87 there would be a subtraction, from the sum valid in row 84, of the numbers in the column's rows one, two, and three. And so on. But that pattern did not hold in the other tables. These columns contain only numbers; any numbers in headers are spelled out. No nested tables, split cells, or merged cells. And again, it worked fine in Word 2000 PC, with columns that were several hundred rows in length. I'm very perplexed.
> If you're using a formula like {=SUM(ABOVE)}, there are numerous reasons why this might not give the expected result. For details,
> plus info on doing a wide range of calculations in Word, check out my Word Field Maths Tutorial, at:
> http://lounge.windowssecrets.com/index.php?showtopic=365442
> or
> http://www.gmayor.com/downloads.htm#Third_party
>
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
>
>
> wrote in message news:[email protected]...
> > Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I create extensive tables in Word and use the formula
> > function to add columns for totals. This worked great on the PC but on the Mac there seems to be a point at which the formula
> > "deactivates" and all that shows in the cell is the equal sign. I can type in the formula but the result it gives me is wrong. I
> > cannot find the pattern in this. I thought at first it was an issue with converting a PC file to a Mac file but I encounter the
> > same problem in files I create fresh on the Mac (.docx) as well. I need this functionality for my work ... anyone know what's
> > happening or have any solutions?
>
>
 
J

John McGhie

I think I should save you a lot of trouble:

Formula fields in tables are not, never have been, and never will be
reliable. The feature has not changed in several versions, and it's not
even described properly in the Help any longer.

I suggest that for your purposes, using embedded Excel spreadsheets would be
far more appropriate. And much more reliable!

Sorry... But formula fields in tables were never designed for anything that
contains more than half a dozen rows :)

Cheers


Thanks, your tutorial is really great and covers all the struggles I stumbled
through when figuring out how to work with tables ... would that I'd known of
it back then!

However, the problem I'm encountering is that =SUM(ABOVE) in tables which
worked fine in Word 2000 on the PC do not work when resaved in Word for Mac
2008, whether saved in Word 97-2004 format or in Word .docx format. Nor in new
tables that I create. The formula gives the appearance of working but the sum
is incorrect. I know I've hit the cell where he problem will start because
instead of presenting the formula =SUM(ABOVE) as for the preceeding cells, it
simply presents the =. From that point on, the formula does not work correctly
although it does present a sum result. I thought it might be some sort of
limit on the count of items the formula will sum in Word for Mac 2008; in
three of the tables, the problem point is occurs at row 85 and in one table at
row 84...close but not exact.

In the row 84 table, which I converted from PC to Mac, I detected a pattern in
which the =SUM(ABOVE) began to SUBTRACT the numbers, in order, from the start
of the column! So, for example, in row 87 there would be a subtraction, from
the sum valid in row 84, of the numbers in the column's rows one, two, and
three. And so on. But that pattern did not hold in the other tables. These
columns contain only numbers; any numbers in headers are spelled out. No
nested tables, split cells, or merged cells. And again, it worked fine in Word
2000 PC, with columns that were several hundred rows in length. I'm very
perplexed.
If you're using a formula like {=SUM(ABOVE)}, there are numerous reasons why
this might not give the expected result. For details,
plus info on doing a wide range of calculations in Word, check out my Word
Field Maths Tutorial, at:
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party


--
Cheers
macropod
[Microsoft MVP - Word]


wrote in message news:[email protected]...
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I
create extensive tables in Word and use the formula
function to add columns for totals. This worked great on the PC but on the
Mac there seems to be a point at which the formula
"deactivates" and all that shows in the cell is the equal sign. I can type
in the formula but the result it gives me is wrong. I
cannot find the pattern in this. I thought at first it was an issue with
converting a PC file to a Mac file but I encounter the
same problem in files I create fresh on the Mac (.docx) as well. I need this
functionality for my work ... anyone know what's
happening or have any solutions?

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]
 
M

macropod

hi John,

I suspect it's more of an issue with a bug in Mac Word, than "formula fields in tables were never designed for anything that
contains more than half a dozen rows". Add the OP said, the fields work fine in PC Word. Plus, it's impossible to bookmark anything
in an embedded Excel workbook for use in a cross-reference elsewhere in the document.

--
Cheers
macropod
[Microsoft MVP - Word]


John McGhie said:
I think I should save you a lot of trouble:

Formula fields in tables are not, never have been, and never will be
reliable. The feature has not changed in several versions, and it's not
even described properly in the Help any longer.

I suggest that for your purposes, using embedded Excel spreadsheets would be
far more appropriate. And much more reliable!

Sorry... But formula fields in tables were never designed for anything that
contains more than half a dozen rows :)

Cheers


Thanks, your tutorial is really great and covers all the struggles I stumbled
through when figuring out how to work with tables ... would that I'd known of
it back then!

However, the problem I'm encountering is that =SUM(ABOVE) in tables which
worked fine in Word 2000 on the PC do not work when resaved in Word for Mac
2008, whether saved in Word 97-2004 format or in Word .docx format. Nor in new
tables that I create. The formula gives the appearance of working but the sum
is incorrect. I know I've hit the cell where he problem will start because
instead of presenting the formula =SUM(ABOVE) as for the preceeding cells, it
simply presents the =. From that point on, the formula does not work correctly
although it does present a sum result. I thought it might be some sort of
limit on the count of items the formula will sum in Word for Mac 2008; in
three of the tables, the problem point is occurs at row 85 and in one table at
row 84...close but not exact.

In the row 84 table, which I converted from PC to Mac, I detected a pattern in
which the =SUM(ABOVE) began to SUBTRACT the numbers, in order, from the start
of the column! So, for example, in row 87 there would be a subtraction, from
the sum valid in row 84, of the numbers in the column's rows one, two, and
three. And so on. But that pattern did not hold in the other tables. These
columns contain only numbers; any numbers in headers are spelled out. No
nested tables, split cells, or merged cells. And again, it worked fine in Word
2000 PC, with columns that were several hundred rows in length. I'm very
perplexed.
If you're using a formula like {=SUM(ABOVE)}, there are numerous reasons why
this might not give the expected result. For details,
plus info on doing a wide range of calculations in Word, check out my Word
Field Maths Tutorial, at:
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party


--
Cheers
macropod
[Microsoft MVP - Word]


wrote in message Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I
create extensive tables in Word and use the formula
function to add columns for totals. This worked great on the PC but on the
Mac there seems to be a point at which the formula
"deactivates" and all that shows in the cell is the equal sign. I can type
in the formula but the result it gives me is wrong. I
cannot find the pattern in this. I thought at first it was an issue with
converting a PC file to a Mac file but I encounter the
same problem in files I create fresh on the Mac (.docx) as well. I need this
functionality for my work ... anyone know what's
happening or have any solutions?

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

--

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]
 
M

macropod

Hi dsr,

I suspect it's a bug in Word 2008. Perhaps you'll get better results by referencing the cell range explicitly (eg {=SUM(A1:A100)}).

--
Cheers
macropod
[Microsoft MVP - Word]


Thanks, your tutorial is really great and covers all the struggles I stumbled through when figuring out how to work with tables
... would that I'd known of it back then!

However, the problem I'm encountering is that =SUM(ABOVE) in tables which worked fine in Word 2000 on the PC do not work when
resaved in Word for Mac 2008, whether saved in Word 97-2004 format or in Word .docx format. Nor in new tables that I create. The
formula gives the appearance of working but the sum is incorrect. I know I've hit the cell where he problem will start because
instead of presenting the formula =SUM(ABOVE) as for the preceeding cells, it simply presents the =. From that point on, the
formula does not work correctly although it does present a sum result. I thought it might be some sort of limit on the count of
items the formula will sum in Word for Mac 2008; in three of the tables, the problem point is occurs at row 85 and in one table at
row 84...close but not exact.

In the row 84 table, which I converted from PC to Mac, I detected a pattern in which the =SUM(ABOVE) began to SUBTRACT the
numbers, in order, from the start of the column! So, for example, in row 87 there would be a subtraction, from the sum valid in
row 84, of the numbers in the column's rows one, two, and three. And so on. But that pattern did not hold in the other tables.
These columns contain only numbers; any numbers in headers are spelled out. No nested tables, split cells, or merged cells. And
again, it worked fine in Word 2000 PC, with columns that were several hundred rows in length. I'm very perplexed.
If you're using a formula like {=SUM(ABOVE)}, there are numerous reasons why this might not give the expected result. For
details,
plus info on doing a wide range of calculations in Word, check out my Word Field Maths Tutorial, at:
http://lounge.windowssecrets.com/index.php?showtopic=365442
or
http://www.gmayor.com/downloads.htm#Third_party


--
Cheers
macropod
[Microsoft MVP - Word]


wrote in message news:[email protected]...
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I create extensive tables in Word and use the formula
function to add columns for totals. This worked great on the PC but on the Mac there seems to be a point at which the formula
"deactivates" and all that shows in the cell is the equal sign. I can type in the formula but the result it gives me is wrong.
I
cannot find the pattern in this. I thought at first it was an issue with converting a PC file to a Mac file but I encounter the
same problem in files I create fresh on the Mac (.docx) as well. I need this functionality for my work ... anyone know what's
happening or have any solutions?
 
D

dsr

Thanks, all, for your ideas and comments. It does seem more likely to be a bug issue, as it worked fine in Word for PC. I'll see if I can work it out through Microsoft. dsr
 
C

CyberTaz

Meaning no disrespect to either of the other responders, but I was a bit
surprised by some of the remarks from each. Just for fun I copied a list of
approximately 110 values from an Excel worksheet, pasted to a Word document,
converted to a table, then added a row at the bottom & simply used Table>
Formula, =SUM(ABOVE). I did the same thing in Mac Word 2004, Mac Word 2008,
& Win Word 2003 with exactly the same results.

The function works perfectly in all 3 versions of Word *unless* the column
includes cells which do not contain values. Each time I delete one of those
cells working my way up the table & update the field it re-totals accurately
& once all the non-value cells are eliminated the field updates to the
correct total for the remaining 78 values -- I've verified the sums all
along the way in Excel. IOW, I'm seeing no evidence of any "bug".

Regards |:>)
Bob Jones
[MVP] Office:Mac
 
D

dsr

Thanks, CyberTaz, for putting time and effort into working this problem.

What happens if you add enough valid cells to push the value count (row count) past 85? That seems to be the breaking point in the tables I've created. I learned the hard way about empty cells early on in using tables this way. :) I don't work in Excel so I don't know whether it makes a difference to import from there, although I've considered that I might need to use Excel rather than Word now for this purpose, even though the bulk of the document is columns of text.

dsr
 
C

CyberTaz

Apparently I didn't go far enough with my testing :) I've now tried the
same experiment using 100 rows and reproduce what I understand your findings
to be in Word 2004 as well as Word 2008. What actually happens here is that
when I go into Table> Formula the "Formula:" edit field displays only the =
rather than =SUM(ABOVE). I'm able to select it from the "Paste Function:"
list or type it in, but either way the result is incorrect.

However, if I follow macropod's suggestion to write the formula as
=SUM(A1:A100) it works like a charm in both '04 & '08.

Regards |:>)
Bob Jones
[MVP] Office:Mac
 
D

dsr

Okay, thanks so much for the extra effort. That's exactly the issue I've encountered. I was intrigued when in the one table I discovered the pattern of subtracting from the top of the column but haven't had that happen in any other tables so perhaps that's only coincidental. I'll use the suggested work-around. So it does appear this is a bug in Word for Mac. dsr
 
P

Peter Jamieson

FWIW it is possible to reference cells in embedded spreadsheets via the
LINK mechanism, at least on some versions of Word.

But it doesn't work on (recent) Mac Office at all, at least not on
Intel/Snow Leopard. It used to work on Word 98.

(I suspect that the necessary part of the LINK mechanism no longer
exists on Mac, since Mac tries to insert a DDEAUTO field instead of a
LINK field when pasting a link in this situation).

Also, it's another of those things that only works with .doc and ".xls"
(i.e. Word 97-2003 worksheets), not .docx or .xlsx/.xslm in Word 2007.

e.g. if you create a document, save it so it has a file name, insert an
Excel object, select a cell, then Edit->Copy, Edit->Paste Special and
Paste Link, you do in fact get a LINK to the embedded spreadsheet cell.
These links specify the filename of the containing document, then a
subset name like

_1326921753!Sheet1!R7C3

You can even paste a link in another embedded Excel object in the same
Word document.

I wouldn't personally recommend using such stuff, but the facilities do
exist (or did, depending on how you look at it).

Peter Jamieson

http://tips.pjmsn.me.uk
 
M

macropod

Hi Peter,

Whenever I've tried linking to a cell in an embedded workbook, I've found that the link worked only for as long as the Word document
remained open (Word 2000). Once the document had been saved and closed, the link failed upon re-opening the document.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top