Sub-totaling and Totaling a column of numbers

[diane]

Hi Folks!

I'm using Word X Service Release 1 on my Powerbook G4 with OS 10.3.7...

I created an invoice in Word, where the extended total of each item is
in the same column that the Sub-total and Total fields are located.

I need the Final Total field (Text53) to add only the Sub-total
(Text51) and Sales Tax (Text52) fields together but instead the Final
Total field is adding the entire column together, essentially charging
the customer double for everything. The price of each product is
included in the Sub-total field and then added again in the Final Total
field.

My field names are Text43 thru Text53 and my calculation in Text53 says
=sum(Text51,Text52) yet what actually keeps adding together is Text43
thru Text52.

Can anyone tell me what I'm doing wrong?

Thank you so much!
Diane

 

[CyberTaz]

Hi Diane-

I played around with Word '04 and couldn't create the problem you are
having, so I don't know if this is on target or not. I have noticed
that when I edit a form that uses textbox & calculation fields that
their references will be regenerated if you recreate them. Certain
other editing will cause similar behavior. In other words, what was
(Text52) when you first created the Total calculation may not still be
(Text52). Not a fault or a bug, but something that can simply go
unnoticed and cause similar situations.

As a suggestion, re-check your calc field and make sure that the
references are still correct. If you have already check this out and
confirmed the appropriate references, there must be something else
going on.

Hope this is of som help |:>)

 

[Clive Huggan]

Hello Diane, and anyone else watching,

This is a possible alternative:


In Word 5.1 there was a very useful capability to total a series of numbers
by selecting them and keying Command-["equal" sign].


The capability is available in Word 2001, X and 2004, though not on a
default menu, from a "Tools Calculate" button. As in Word 5.1, it evaluates
selected numbers as a mathematical expression and displays the result in the
status bar (and the result is also in the clipboard, ready to be pasted
where you want). You can select numbers by Option-dragging as well as by
selecting paragraphs, cells or lines. The command is available via Tools
menu --> Customize --> Commands tab [make sure "Normal" is in the "Appears
in" pop-down window at bottom left] --> click on Tools in "Categories"
scroll-box --> scroll to bottom of "Commands" scroll-box (because the items
are not in alphabetical order) --> click on Tools Calculate --> drag to
desired toolbar.


This command is best invoked by adding a button to a toolbar, or assigning a
keystroke (such as Command-["equal" sign].


As you know, Word 2001 and later also have a more powerful feature for
numbers in tables via the Table menu --> Formula ‹ but if the column
contains a blank cell, or a blank paragraph mark in a cell, the feature
doesn't work, which is not a problem with the Tools Calculate command.

Cheers,

Clive Huggan
Canberra, Australia
(My time zone is at least 5 hours different from the US and Europe, so my
follow-on responses to those regions can be delayed)
============================================================

* WAIT FOR CONSIDERED ADVICE: If you post a question, keep re-visiting the
newsgroup for several days after the first response comes in. Sometimes it
takes a few responses before the best or complete solution is proposed;
sometimes you'll be asked for further information so that a better answer
can be provided. Good tips about getting the best out of posting are at
http://www.word.mvps.org/FindHelp/Posting.htm (if you use Safari and it
gives you a blank page the first time, you may need to hit the circular
arrow icon -- "Reload the current page" -- a few times).

============================================================

 

[John McGhie [MVP - Word and Word Macintosh]]

I do my invoices as a prettied-up Excel spreadsheet.

There comes a time in calculations in Word when it is simply easier to use
the application designed for the job.

You can either embed a small Excel table in your Word document to handle the
calculations, or you can embed individual cells from the spreadsheet at the
positions you want the numbers to appear, or you can do what I do and format
the entire invoice in Excel.

Actually, my invoice is a two-worksheet affair that has the timesheet on one
sheet and the invoice on the other. They're linked, with conditional
formatting to alert me when I have the dates wrong.

When dealing with things such as currency calculations you run into
questions of floating point precision. I believe it's just a lot safer to
use Excel's industrial-strength calculation engine when the consequences of
a wrong answer can be very expensive.

Hope this helps

Hi Folks!

I'm using Word X Service Release 1 on my Powerbook G4 with OS 10.3.7...

I created an invoice in Word, where the extended total of each item is
in the same column that the Sub-total and Total fields are located.

I need the Final Total field (Text53) to add only the Sub-total
(Text51) and Sales Tax (Text52) fields together but instead the Final
Total field is adding the entire column together, essentially charging
the customer double for everything. The price of each product is
included in the Sub-total field and then added again in the Final Total
field.

My field names are Text43 thru Text53 and my calculation in Text53 says
=sum(Text51,Text52) yet what actually keeps adding together is Text43
thru Text52.

Can anyone tell me what I'm doing wrong?

Thank you so much!
Diane

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 4 1209 1410

 

[diane]

Hi John!

I'm back, and I have Office 2004 now...

I was wondering, do you have any difficulties with alignment when using
a spreadsheet to make your invoice?

When I use Word I have the capability to move the divisor line between
two cells in one row without disturbing the rest of the column.

It appears that I don't have that option when using Excel. If I want
to move the divisor line between two cells in one particular row it
resizes the entire column rather than just the cell(s) I need resized
in that one row.

So, if I've created the top part of the invoice with the cells all
sized to fit what I need, then I start working on the lower part of the
invoice where I need the cells to be differently sized, when I try to
size the cells accordingly it resizes the entire column which in turn
messes up the top part of the form.

Sometimes my questions are so hard to ask, I hope that I've made
sense... While I'm waiting to hear back I'm going to go in and see if
there's anything I can answer for anyone.

Thanks John, have a great day!
Diane Dennis

 

[John McGhie [MVP - Word and Word Macintosh]]

Hi Diane:

Excel Spreadsheets are NOT Word tables, and don't work anything like them
I am surprised that the "other" John has not descended upon your head
with howls of anguish, because HE is an MVP in Excel, and knows damned well
that *I* can't add up a shopping list in that product.

The short answer to your question is: Text in excel can span multiple
cells, you do not have to resize the cell containing the text, just let the
text run across the neighbouring cells.

Read the Help on formatting in Excel to get you started: it's a very
different product to Word. In Excel, you start by sizing the columns to fit
the SMALLEST grid you need, then span multiple columns with the stuff that's
too wide to fit them.

Hop over to the Excel group and give the other John a prod if you need more
than that. Or email me direct and I will send you a copy of my invoice you
can play with

Cheers

Hi John!

I'm back, and I have Office 2004 now...

I was wondering, do you have any difficulties with alignment when using
a spreadsheet to make your invoice?

When I use Word I have the capability to move the divisor line between
two cells in one row without disturbing the rest of the column.

It appears that I don't have that option when using Excel. If I want
to move the divisor line between two cells in one particular row it
resizes the entire column rather than just the cell(s) I need resized
in that one row.

So, if I've created the top part of the invoice with the cells all
sized to fit what I need, then I start working on the lower part of the
invoice where I need the cells to be differently sized, when I try to
size the cells accordingly it resizes the entire column which in turn
messes up the top part of the form.

Sometimes my questions are so hard to ask, I hope that I've made
sense... While I'm waiting to hear back I'm going to go in and see if
there's anything I can answer for anyone.

Thanks John, have a great day!
Diane Dennis

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Microsoft MVP, Word and Word for Macintosh. Consultant Technical Writer
Sydney, Australia +61 4 1209 1410