A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Tables
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

calculations in tables



 
 
Thread Tools Display Modes
  #1  
Old June 22nd, 2007, 09:51 AM posted to microsoft.public.word.tables
LM
external usenet poster
 
Posts: 47
Default calculations in tables

I am doing calculations in tables in Word but am having trouble with part of
the calculation. In cell A4, I want to insert multiple IF tests. I want to
say that if cell A3=10, make the number in cell A4=1, if cell A3=12, make the
number in cell A4=3, if cell A3=14, make the number in cell A4=5, etc., etc.
Cell A3 could calculate to any of five numbers and basically if it calculates
to a double number, I want it to add the two numbers together and put a
single number in cell A4. If, however, cell A3 is only a single number, I
just want that single number copied to cell A4.

Can someone please tell me how to do this. I have limited knowledge of
calculations in Word and am just learning as I go along. So far I have
managed but this one has got me beaten.

Thanks in anticipation of any replies.
  #2  
Old June 22nd, 2007, 10:53 AM posted to microsoft.public.word.tables
Graham Mayor
external usenet poster
 
Posts: 18,297
Default calculations in tables

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1) }) }"
"{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must force an
update when the content of cell A3 changes. the simplest method is to attach
an update macro to a toolbar - http://www.gmayor.com/installing_macro.htm
Note A3 in the calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble with
part of the calculation. In cell A4, I want to insert multiple IF
tests. I want to say that if cell A3=10, make the number in cell
A4=1, if cell A3=12, make the number in cell A4=3, if cell A3=14,
make the number in cell A4=5, etc., etc. Cell A3 could calculate to
any of five numbers and basically if it calculates to a double
number, I want it to add the two numbers together and put a single
number in cell A4. If, however, cell A3 is only a single number, I
just want that single number copied to cell A4.

Can someone please tell me how to do this. I have limited knowledge
of calculations in Word and am just learning as I go along. So far I
have managed but this one has got me beaten.

Thanks in anticipation of any replies.



  #3  
Old June 22nd, 2007, 11:34 AM posted to microsoft.public.word.tables
LM
external usenet poster
 
Posts: 47
Default calculations in tables

In cell A3, I am calculating the number 2 by the value in cell A2. The value
in cell A2 can only be 0 to 9 (a single digit number). If the total that
ends up in cell A3 is two numbers, I want it to add those two numbers
together and put them in cell A4. If the total that ends up in A3 is a
single number, I want that single number in cell A4.

Your calculation worked perfectly until I got up to the numbers 8 and 9 in
cell A2 (which total 16 and 18 in cell A3 and should put 7 or 8 in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1) }) }"
"{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must force an
update when the content of cell A3 changes. the simplest method is to attach
an update macro to a toolbar - http://www.gmayor.com/installing_macro.htm
Note A3 in the calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble with
part of the calculation. In cell A4, I want to insert multiple IF
tests. I want to say that if cell A3=10, make the number in cell
A4=1, if cell A3=12, make the number in cell A4=3, if cell A3=14,
make the number in cell A4=5, etc., etc. Cell A3 could calculate to
any of five numbers and basically if it calculates to a double
number, I want it to add the two numbers together and put a single
number in cell A4. If, however, cell A3 is only a single number, I
just want that single number copied to cell A4.

Can someone please tell me how to do this. I have limited knowledge
of calculations in Word and am just learning as I go along. So far I
have managed but this one has got me beaten.

Thanks in anticipation of any replies.




  #4  
Old June 22nd, 2007, 01:15 PM posted to microsoft.public.word.tables
Graham Mayor
external usenet poster
 
Posts: 18,297
Default calculations in tables

Plan B

{ IF{ =A3 } 9 "{ =({ =INT({ =(A3)/10 }) } + {=(A3) \# "x"})}" "{ =A3 }" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


LM wrote:
In cell A3, I am calculating the number 2 by the value in cell A2.
The value in cell A2 can only be 0 to 9 (a single digit number). If
the total that ends up in cell A3 is two numbers, I want it to add
those two numbers together and put them in cell A4. If the total
that ends up in A3 is a single number, I want that single number in
cell A4.

Your calculation worked perfectly until I got up to the numbers 8 and
9 in cell A2 (which total 16 and 18 in cell A3 and should put 7 or 8
in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1)
}) }" "{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must
force an update when the content of cell A3 changes. the simplest
method is to attach an update macro to a toolbar -
http://www.gmayor.com/installing_macro.htm Note A3 in the
calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble with
part of the calculation. In cell A4, I want to insert multiple IF
tests. I want to say that if cell A3=10, make the number in cell
A4=1, if cell A3=12, make the number in cell A4=3, if cell A3=14,
make the number in cell A4=5, etc., etc. Cell A3 could calculate to
any of five numbers and basically if it calculates to a double
number, I want it to add the two numbers together and put a single
number in cell A4. If, however, cell A3 is only a single number, I
just want that single number copied to cell A4.

Can someone please tell me how to do this. I have limited knowledge
of calculations in Word and am just learning as I go along. So far
I have managed but this one has got me beaten.

Thanks in anticipation of any replies.



  #5  
Old June 22nd, 2007, 01:26 PM posted to microsoft.public.word.tables
LM
external usenet poster
 
Posts: 47
Default calculations in tables

Thank you for the quick response. I am going to try that now and will let
you know how I go.

I have another calculation I don't know how to do.

If I have the following in an Excel document:

=IF(A3=0,0,10-A3)

how would I do the same thing in a table cell in a Word document.

I have been given an Excel document containing heaps of calculations and
have been asked to do the same calculations in a Word document - I am almost
up to the final step.

Lyn.

"Graham Mayor" wrote:

Plan B

{ IF{ =A3 } 9 "{ =({ =INT({ =(A3)/10 }) } + {=(A3) \# "x"})}" "{ =A3 }" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


LM wrote:
In cell A3, I am calculating the number 2 by the value in cell A2.
The value in cell A2 can only be 0 to 9 (a single digit number). If
the total that ends up in cell A3 is two numbers, I want it to add
those two numbers together and put them in cell A4. If the total
that ends up in A3 is a single number, I want that single number in
cell A4.

Your calculation worked perfectly until I got up to the numbers 8 and
9 in cell A2 (which total 16 and 18 in cell A3 and should put 7 or 8
in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1)
}) }" "{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must
force an update when the content of cell A3 changes. the simplest
method is to attach an update macro to a toolbar -
http://www.gmayor.com/installing_macro.htm Note A3 in the
calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble with
part of the calculation. In cell A4, I want to insert multiple IF
tests. I want to say that if cell A3=10, make the number in cell
A4=1, if cell A3=12, make the number in cell A4=3, if cell A3=14,
make the number in cell A4=5, etc., etc. Cell A3 could calculate to
any of five numbers and basically if it calculates to a double
number, I want it to add the two numbers together and put a single
number in cell A4. If, however, cell A3 is only a single number, I
just want that single number copied to cell A4.

Can someone please tell me how to do this. I have limited knowledge
of calculations in Word and am just learning as I go along. So far
I have managed but this one has got me beaten.

Thanks in anticipation of any replies.




  #6  
Old June 22nd, 2007, 01:43 PM posted to microsoft.public.word.tables
LM
external usenet poster
 
Posts: 47
Default calculations in tables

Thank you so much Graham. That worked beautifully. I never would have been
able to do that by myself.

Apart from the extra calculation I need to do listed below, the only other
thing I would like to be able to do is CONCATENATE. I can't find anywhere in
the help file that this is available in Word. If it is possible to
concatenate, can you please let me know how. If it isn't possible, this -
=IF(A3=0,0,10-A3) - as listed below - is the final thing I need to do to
finish my calculation.

"LM" wrote:

Thank you for the quick response. I am going to try that now and will let
you know how I go.

I have another calculation I don't know how to do.

If I have the following in an Excel document:

=IF(A3=0,0,10-A3)

how would I do the same thing in a table cell in a Word document.

I have been given an Excel document containing heaps of calculations and
have been asked to do the same calculations in a Word document - I am almost
up to the final step.

Lyn.

"Graham Mayor" wrote:

Plan B

{ IF{ =A3 } 9 "{ =({ =INT({ =(A3)/10 }) } + {=(A3) \# "x"})}" "{ =A3 }" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


LM wrote:
In cell A3, I am calculating the number 2 by the value in cell A2.
The value in cell A2 can only be 0 to 9 (a single digit number). If
the total that ends up in cell A3 is two numbers, I want it to add
those two numbers together and put them in cell A4. If the total
that ends up in A3 is a single number, I want that single number in
cell A4.

Your calculation worked perfectly until I got up to the numbers 8 and
9 in cell A2 (which total 16 and 18 in cell A3 and should put 7 or 8
in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1)
}) }" "{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must
force an update when the content of cell A3 changes. the simplest
method is to attach an update macro to a toolbar -
http://www.gmayor.com/installing_macro.htm Note A3 in the
calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble with
part of the calculation. In cell A4, I want to insert multiple IF
tests. I want to say that if cell A3=10, make the number in cell
A4=1, if cell A3=12, make the number in cell A4=3, if cell A3=14,
make the number in cell A4=5, etc., etc. Cell A3 could calculate to
any of five numbers and basically if it calculates to a double
number, I want it to add the two numbers together and put a single
number in cell A4. If, however, cell A3 is only a single number, I
just want that single number copied to cell A4.

Can someone please tell me how to do this. I have limited knowledge
of calculations in Word and am just learning as I go along. So far
I have managed but this one has got me beaten.

Thanks in anticipation of any replies.




  #7  
Old June 22nd, 2007, 01:56 PM posted to microsoft.public.word.tables
Graham Mayor
external usenet poster
 
Posts: 18,297
Default calculations in tables

The other calculation would be

{ IF {=A3 } = 0 "0" "{ =(10 - { =A3 } )}" }

Again calculations will not auto-update as they do in Excel
The Concatenate function if not available in Word. Whether there is a
workaround will depend on what you are trying to do. Frankly if complex
calculations are involved, it is usually simpler to insert the relevant
Excel table.


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



LM wrote:
Thank you so much Graham. That worked beautifully. I never would
have been able to do that by myself.

Apart from the extra calculation I need to do listed below, the only
other thing I would like to be able to do is CONCATENATE. I can't
find anywhere in the help file that this is available in Word. If it
is possible to concatenate, can you please let me know how. If it
isn't possible, this - =IF(A3=0,0,10-A3) - as listed below - is the
final thing I need to do to finish my calculation.

"LM" wrote:

Thank you for the quick response. I am going to try that now and
will let you know how I go.

I have another calculation I don't know how to do.

If I have the following in an Excel document:

=IF(A3=0,0,10-A3)

how would I do the same thing in a table cell in a Word document.

I have been given an Excel document containing heaps of calculations
and have been asked to do the same calculations in a Word document -
I am almost up to the final step.

Lyn.

"Graham Mayor" wrote:

Plan B

{ IF{ =A3 } 9 "{ =({ =INT({ =(A3)/10 }) } + {=(A3) \# "x"})}" "{
=A3 }" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


LM wrote:
In cell A3, I am calculating the number 2 by the value in cell A2.
The value in cell A2 can only be 0 to 9 (a single digit number).
If the total that ends up in cell A3 is two numbers, I want it to
add those two numbers together and put them in cell A4. If the
total that ends up in A3 is a single number, I want that single
number in cell A4.

Your calculation worked perfectly until I got up to the numbers 8
and 9 in cell A2 (which total 16 and 18 in cell A3 and should put
7 or 8 in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1)
}) }" "{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must
force an update when the content of cell A3 changes. the simplest
method is to attach an update macro to a toolbar -
http://www.gmayor.com/installing_macro.htm Note A3 in the
calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble
with part of the calculation. In cell A4, I want to insert
multiple IF tests. I want to say that if cell A3=10, make the
number in cell A4=1, if cell A3=12, make the number in cell
A4=3, if cell A3=14, make the number in cell A4=5, etc., etc.
Cell A3 could calculate to any of five numbers and basically if
it calculates to a double number, I want it to add the two
numbers together and put a single number in cell A4. If,
however, cell A3 is only a single number, I just want that
single number copied to cell A4.

Can someone please tell me how to do this. I have limited
knowledge of calculations in Word and am just learning as I go
along. So far I have managed but this one has got me beaten.

Thanks in anticipation of any replies.



  #8  
Old June 22nd, 2007, 02:17 PM posted to microsoft.public.word.tables
LM
external usenet poster
 
Posts: 47
Default calculations in tables

Thank you so much for your help. I can get around not being able to
concatenate and I know you have to autoupdate. I can't believe I have
finally finished - I never could have done it without your help.



"Graham Mayor" wrote:

The other calculation would be

{ IF {=A3 } = 0 "0" "{ =(10 - { =A3 } )}" }

Again calculations will not auto-update as they do in Excel
The Concatenate function if not available in Word. Whether there is a
workaround will depend on what you are trying to do. Frankly if complex
calculations are involved, it is usually simpler to insert the relevant
Excel table.


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



LM wrote:
Thank you so much Graham. That worked beautifully. I never would
have been able to do that by myself.

Apart from the extra calculation I need to do listed below, the only
other thing I would like to be able to do is CONCATENATE. I can't
find anywhere in the help file that this is available in Word. If it
is possible to concatenate, can you please let me know how. If it
isn't possible, this - =IF(A3=0,0,10-A3) - as listed below - is the
final thing I need to do to finish my calculation.

"LM" wrote:

Thank you for the quick response. I am going to try that now and
will let you know how I go.

I have another calculation I don't know how to do.

If I have the following in an Excel document:

=IF(A3=0,0,10-A3)

how would I do the same thing in a table cell in a Word document.

I have been given an Excel document containing heaps of calculations
and have been asked to do the same calculations in a Word document -
I am almost up to the final step.

Lyn.

"Graham Mayor" wrote:

Plan B

{ IF{ =A3 } 9 "{ =({ =INT({ =(A3)/10 }) } + {=(A3) \# "x"})}" "{
=A3 }" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


LM wrote:
In cell A3, I am calculating the number 2 by the value in cell A2.
The value in cell A2 can only be 0 to 9 (a single digit number).
If the total that ends up in cell A3 is two numbers, I want it to
add those two numbers together and put them in cell A4. If the
total that ends up in A3 is a single number, I want that single
number in cell A4.

Your calculation worked perfectly until I got up to the numbers 8
and 9 in cell A2 (which total 16 and 18 in cell A3 and should put
7 or 8 in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - { =ROUND(A3,-1)
}) }" "{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You must
force an update when the content of cell A3 changes. the simplest
method is to attach an update macro to a toolbar -
http://www.gmayor.com/installing_macro.htm Note A3 in the
calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble
with part of the calculation. In cell A4, I want to insert
multiple IF tests. I want to say that if cell A3=10, make the
number in cell A4=1, if cell A3=12, make the number in cell
A4=3, if cell A3=14, make the number in cell A4=5, etc., etc.
Cell A3 could calculate to any of five numbers and basically if
it calculates to a double number, I want it to add the two
numbers together and put a single number in cell A4. If,
however, cell A3 is only a single number, I just want that
single number copied to cell A4.

Can someone please tell me how to do this. I have limited
knowledge of calculations in Word and am just learning as I go
along. So far I have managed but this one has got me beaten.

Thanks in anticipation of any replies.




  #9  
Old June 22nd, 2007, 03:04 PM posted to microsoft.public.word.tables
Graham Mayor
external usenet poster
 
Posts: 18,297
Default calculations in tables

You are welcome

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

LM wrote:
Thank you so much for your help. I can get around not being able to
concatenate and I know you have to autoupdate. I can't believe I have
finally finished - I never could have done it without your help.



"Graham Mayor" wrote:

The other calculation would be

{ IF {=A3 } = 0 "0" "{ =(10 - { =A3 } )}" }

Again calculations will not auto-update as they do in Excel
The Concatenate function if not available in Word. Whether there is a
workaround will depend on what you are trying to do. Frankly if
complex calculations are involved, it is usually simpler to insert
the relevant Excel table.


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



LM wrote:
Thank you so much Graham. That worked beautifully. I never would
have been able to do that by myself.

Apart from the extra calculation I need to do listed below, the only
other thing I would like to be able to do is CONCATENATE. I can't
find anywhere in the help file that this is available in Word. If
it is possible to concatenate, can you please let me know how. If
it isn't possible, this - =IF(A3=0,0,10-A3) - as listed below - is
the final thing I need to do to finish my calculation.

"LM" wrote:

Thank you for the quick response. I am going to try that now and
will let you know how I go.

I have another calculation I don't know how to do.

If I have the following in an Excel document:

=IF(A3=0,0,10-A3)

how would I do the same thing in a table cell in a Word document.

I have been given an Excel document containing heaps of
calculations and have been asked to do the same calculations in a
Word document - I am almost up to the final step.

Lyn.

"Graham Mayor" wrote:

Plan B

{ IF{ =A3 } 9 "{ =({ =INT({ =(A3)/10 }) } + {=(A3) \# "x"})}" "{
=A3 }" }


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


LM wrote:
In cell A3, I am calculating the number 2 by the value in cell
A2. The value in cell A2 can only be 0 to 9 (a single digit
number). If the total that ends up in cell A3 is two numbers, I
want it to add those two numbers together and put them in cell
A4. If the total that ends up in A3 is a single number, I want
that single number in cell A4.

Your calculation worked perfectly until I got up to the numbers 8
and 9 in cell A2 (which total 16 and 18 in cell A3 and should put
7 or 8 in cell A4).

Nearly there!!


"Graham Mayor" wrote:

The following placed in cell A4 will probably do the trick

{ IF { =A3 } 9 "{ ={ =ROUND(A3,-1) } /10 + (A3 - {
=ROUND(A3,-1) }) }" "{ =A3 }" }

Note that the curly brackets are places with CTRL+F9 (each pair)
Note also that such fields will not automatically update. You
must force an update when the content of cell A3 changes. the
simplest method is to attach an update macro to a toolbar -
http://www.gmayor.com/installing_macro.htm Note A3 in the
calculation is a table cell location and not a REF field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


but note that

LM wrote:
I am doing calculations in tables in Word but am having trouble
with part of the calculation. In cell A4, I want to insert
multiple IF tests. I want to say that if cell A3=10, make the
number in cell A4=1, if cell A3=12, make the number in cell
A4=3, if cell A3=14, make the number in cell A4=5, etc., etc.
Cell A3 could calculate to any of five numbers and basically if
it calculates to a double number, I want it to add the two
numbers together and put a single number in cell A4. If,
however, cell A3 is only a single number, I just want that
single number copied to cell A4.

Can someone please tell me how to do this. I have limited
knowledge of calculations in Word and am just learning as I go
along. So far I have managed but this one has got me beaten.

Thanks in anticipation of any replies.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.