Calculations in a table

[Bruce]

I am trying to perform a calculation in a table. All of
the calculations use numbers the user has entered into
form fields. First I tried to perform the calculation
where I subtracted cell e2 in Table2 from cell b2 in
Table1. I bookmarked Table1, used the Table cell helper
macro to identify the cell, and attempted =Table1 b2-e2.
Syntax error. Then I tried =sum(Table1 b2,-e2. Syntax
error. I also tried both of the above with cell ranges:
=Table1 b2:b2-e2, =sum(Table 1 b2:b2,-e2, etc. Syntax
error. I tried adding a space in front of the minus sign,
and anything else I could think of. No luck. Finally I
merged the tables together, and was able at last to
perform the calculation. I would be interested in knowing
what I did wrong (Help is no help), or if it is even
possible to do what I was trying to do.
Second part: After performing the calculation above in
cell g2, I want the user to be able to go to the next row
and enter a number into e3, and have the calculation in g3
be the result of the previously calculated g2 minus e3.
It works OK, but I would like g3 to remain blank until
there is a value in e3. I see from Help that it is
possible to generate text in the THEN and ELSE components
of an IF statement, but is it possible to generate a
calculation?
I know that I should use Excel, but the formatting
limitations are a pretty big problem in this case.
However, the calculations are the most important, so I
will sacrifice formatting if necessary to obtain the
necessary math results.

 

[Suzanne S. Barnhill]

If you're using form fields, you don't need to bookmark the cells. Each form
field has a built-in bookmark (which means you don't need to keep track of
the tables/cell addresses). You must enable "Calculate on exit" in the Form
Field Options for each field. But also see "WD2000: Calculation in Form
Field Shows Wrong Result" at http://support.microsoft.com/?kbid=211253

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://www.mvps.org/word
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Suzanne S. Barnhill]

I hadn't realized that copied form fields don't have bookmarks (but I just
tested and see that you're right), but you would usually want to change the
bookmark from Text# to something more descriptive anyway. But yes, you can
use the built-in bookmarks just the same as you would any other bookmark.
Calculation in tables is an area in which I have minimal experience, but
there are some examples in the "Field codes: = (Formula) field" Help topic
that might be helpful.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://www.mvps.org/word
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[macropod]

Hi Bruce,

Word's inter-table handling is best described as arcane,
but here's how you can get the results you're after:

1. To refer to a table from outside the table, first
Bookmark the whole Table (eg Table1, Table2 etc).

2. To get the value of a cell (or a range of cells) from
outside a bookmarked table, use a field like:
{=SUM(Table1 a1)} or {=SUM(Table1 a1:c2)}
You MUST include at least one of the following formulae in
the expression:
AVERAGE(), COUNT(), MAX(), MIN(), PRODUCT(), or SUM()
You cannot retrieve text this way; if you need to retrieve
text, then you have to bookmark the text itself - not
including the cell it's in.

3. To work with values from two tables outside those
tables, use a formula like:
{=SUM(Table1 a1)+SUM(Table2 a1)}

4. To blank a cell until there is a value in another cell,
you could use an IF formula, like:
{IF{=COUNT(b2)}= 0 "" {=(b2-b1)}}
(useful if you're evaluating/outputting text or nulls) but
a numeric picture switch is far easier to use with
numbers. A suitable numeric picture switch would be
something like:
\# #;-#;
giving a field like:
{=SUM(Table1 a1:c2) \# #;-#;}
You can even have the numeric picture switch output text!!:
{=SUM(Table1 a1:c2) \# Profit\ $,0.00;Loss\ $,0.00;Break\
Even}

5. As a bonus, if you format the font or background of the
various parts of the numeric picture switch with bold,
colouring, italics etc, those attributes are carried
through to the output!!

Cheers

 

[Suzanne S. Barnhill]

But step 1 is not necessary if form fields have been used.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://www.mvps.org/word
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.