Relative Cell Calculation

[Darlynne]

I have read all the previous posts (8/22 specifically) and
the MVP site about using fields to create relative cell
formulas in tables, but cannot get any of them to work. To
multiply C1 by D1, do I start with Insert Field or Ctrl-F9
and then type this entire string: Quote{Set CellNr1 "b{=
{SEQ CellNr}/2}"}{Set CellNr2 "c{= {SEQ CellNr \c}/2}"}{=
{CellNr1}*{CellNr2}}? If so, nothing happens: I can see
the field in my table, but nothing calculates. Obviously
I'm doing something wrong or not doing something, but I
can't figure out where. Thanks.

 

[Darlynne]

But doesn't that mean the formula is static? I need a
field that will change when a row is added or deleted from
a column. Everything I've read so far indicates I need to
use seq and set fields. The problem is that I don't
understand how they work. I was hoping someone could tell
me what I'm missing to get Quote{Set CellNr1 "b{={={CellNr1}*{CellNr2}} to work down a column of numbers.

 

[Darlynne]

Cindy, thank you. I'm using Word 2000. I'll follow your
steps and let you know how things go. I appreciate your
help.

 

[Greg Maxey]

Cindy,

I have been following this discussion as the process is new to me. I was
able to get the calculation to work but I am puzzled by the process.

As I step through the fields and update I get
SET CellNr1 "d0.5"
SET CellNr2 "c1"
This seems puzzling enough, but when I get down to the formula field the
formula looks like

{=d1*c1} which of course returns the correct answer. What I can sort out is
where and when does the value of CellNr1 "set" earlier to "d0.5" get changed
to "d1"

Thanks
--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[macropod]

Hi Greg,

Starting with the correct syntax for the field:
{Quote{Set CellNr1 "c{={SEQ CellNr}/2}"}{Set CellNr2 "d{=
{SEQ CellNr \c}/2}"}{={CellNr1 }*{CellNr2}}}
the {SEQ CellNr} portion creates a numeric sequence called
CellNr.

Ordinarily, this would increment by 1 each time it is
updated. In a table, though, a 'feature' causes it to
increment by 2 each time it is updated. Hence the need to
divide {SEQ CellNr} by 2.

The next instance of {SEQ CellNr}, in the same cell,
includes the \c switch (i.e. {SEQ CellNr \c}), to refer to
the last-used {SEQ CellNr} value, rather than updating it.
You'll note that {SEQ CellNr} is only used once in each
cell without the \c switch. You'd do this for each cell
reference from within the one cell. For example, to
multiply the value in Col A by Col B and add Col C, you
might use:
{QUOTE{Set CellA "a{={SEQ RowNr}/2}"}{Set CellB "b{={SEQ
RowNr \c}/2}"}{Set CellC "c{={SEQ RowNr \c}/2}"}{={CellA}*
{CellB}+{CellC}}}

If, as you say, you were geting a reference like d0.5, I
suspect that's because you put the /2 in the wrong place
in your field.

Cheers
PS: Remove NO.SPAM from the above before replying.

 

[Greg]

Macropod,

Thanks for attempting to explain. Like I mentioned
before, I am getting the correct result, but I still don't
grasp how I got there. If I put two SEQ fields in a table
or two SEQ fields in a single cell, they count normally
(i.e., 1 and 2). I am not seeing the count by two process
you explained.

If I put the following in a cell {SET CellNR1 "c{={SEQ
CellNR}\2}"} then update just the SEQ field portion, the
result is "c{=1/2}" if I update the formula field portion,
the result is "c0.5"

Now for some unfathomable reason, if I insert a REF field
{REF CellNR1} and update, the result is c1 just like I get
c1 in the QUOTE field!!

Repeating this process with the Set CellNR2 field as you
provided the result seems perfectly normal. The SEQ field
portion returns "d{=2/2}" and the formula field result
is "d1" A {REF CellNR2} produces d1.

How is the "c05" converted to "c1"? That is the part I
can't figure out.

 

[Darlynne]

I made it this far and everything works beautifully,
including adding currency formatting to the field. Thank
you! How then do I use the field in additional tables in
the same document and have the formula restart at the
beginning of each table? I've looked at the switches for
the SEQ field, but perhaps I need a new numeric sequence?
Neither change produced the correct result so I'm still
doing something wrong.

 

[Darlynne]

I changed the SEQ name and everything works. Thanks for
all your help.

 

[macropod]

Hi Greg,

The peculiar behaviour with the way the SEQ fields behaves
is due to the way it's being used to create the cell
references. This is perhaps best explained by way of
illustration, and an alternative approach. You don't even
need a table for most of it.

Suppose you create a field like: {SEQ Cell} Press F9 and
it will return the number 1. The same happens if you then
wrap this field inside a QUOTE field, thus:
{Quote{SEQ Cell}} or {Quote{={SEQ Cell}}} or {Quote "{=
{SEQ Cell}}"}
or even {Quote{Set Call "{={SEQ Cell}}"}{Call}}

However, the moment you try to turn the last one into a
cell reference (b1) by using:
{Quote{Set Call "b{={SEQ Cell}}"}{Call}}
you get b2 instead. Try it with the other iterations and
you'll get various syntax errors.

Due to a bug in the way the SEQ field is being handled,
its value is being doubled when used as illustrated for a
cell reference. So the solution is to halve it, as in:
{Quote{Set Call "b{={SEQ Cell}/2}"}{Call}}.

Extending the above, for which you'd now need a table,
suppose you want to multiply col A by Col B and add Col C.
You could use something like:
{QUOTE{Set CellA "a{={SEQ RowNr}/2}"}{Set CellB "b{={SEQ
RowNr \c}/2}"}{Set CellC "c{={SEQ RowNr \c}/2}"}{={CellA}*
{CellB}+{CellC}}}
This approach both divides the SEQ field results by 2 and
uses the \c switch to stop multiple references to the same
row changing the SEQ No. All fields also retain the
correct values when the document is closed.

You could avoid the need to divide the SEQ field results
by 2 or using the \c switch altogether, by changing the
way the field is coded. For example:
{QUOTE{SET Row {SEQ Row}}{SET ColA "A{=Row}"}{SET ColB "B
{=Row}"}{SET ColC "C{=Row}"}{={ColA}*{ColB}+{ColC}}}
gives the same result as the previous example. It avoids
the need to use the \c switch to stop multiple references
to the same row changing the SEQ No. It does this by only
using the SEQ field once. The same solution also obviates
the need to divide the SEQ values by 2. The downside is
that all rows display the same value when the document is
opened, since the fields will now 'forget' their values
when the document is closed. The next time the document is
opened, all display the last value calculated. The correct
values display immediately the fields are updated, though.

Hope this clarifies things a bit and gives you more
material to amuse yourself with.

Cheers
PS: Remove NO.SPAM from the above before replying.

 

[Greg Maxey]

Macropod,

The fog has lifted. Thank you.

--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL