Trouble with formulas in column...

[diane]

Hi Folks!

I've posted elsewhere throughout here about the invoice I'm trying to
create by embedding an Excel worksheet in a Word document.

I was wondering, the only reason I'm embedding the Excel worksheet is
because I cannot make Word do the calculations I want it to do. Maybe
if I can find out how to do that then I won't have to deal with the
troubles I'm having with the embedded Excel document...

It's a simple invoice and the final column consists of the extended
total for each item, then a subtotal box, then a sales tax box, then a
final total box.

I need the subtotal field to add all the items together and show the
subtotal amount, and then I need the final total to include only the
subtotal field and the sales tax field.

Problem is because they're all in the same column, the subtotal is
adding and showing the subtotal but the final total is adding all the
line items again and then the subtotal and sales tax as well. So, each
line item has been added in twice.

Here's what it should be:

10.00 (Item 1)
10.00 (Item 2)
10.00 (Item 3)
30.00 (Subtotal)
03.00 (Tax)
33.00 (Final Total)

But here's what it is:

10.00 (Item 1)
10.00 (Item 2)
10.00 (Item 3)
30.00 (Subtotal)
03.00 (Tax)
63.00 (Final Total)

Can anyone tell me how to set it up the way I need it?

Thank you!
Diane Dennis

 

[CyberTaz]

It looks like you have used the SUM() function to add up Item 1 through
Tax. Here;s what will help:

Cell A5 10.00
Cell A6 10.00
Cell A7 10.00
Cell A8 =SUM(A5:A7) Result will be 30.00
Cell A9 3.00
Cell A10 =SUM(A8:A9) Result will be 33.00

Please don't be offended, but even thiis is not rhe most productive use
of Excel's capbilities. You might want to look into some training on
the use of the progeam, |:>)

 

[diane]

Please don't be offended, but even thiis is not rhe most productive
use

of Excel's capbilities. You might want to look into some training on
the use of the progeam, |:>)

Aw, I'm not offended but I'm kinda chuckling because I wasn't asking
for help with Excel, I was asking for help with Word. I can do what
you wrote for Excel, it's Word that's adding fields where it
shouldn't...

Field "Text55" 10.00
Field "Text56" 10.00
Field "Text57" 10.00
Field "Text58" =SUM(ABOVE) Result will be 30
Field "Text59" 3.00 (tax)
Field "Text60" =SUM(TEXT58,TEXT59) Result should be $33 but instead is
$63...

Due to issues here and there with embedding Excel worksheets in Word
documents, I'd much rather just use Word to do this. Any more ideas,
but about Word this time?

Thank you, thank you and have a great night and weekend!!
Diane

 

[CyberTaz]

Thanks for your good-natured reception, I misunderstood and thought the
incorrect result was being had in the embedded wksht.

I wish I could give you a better suggestion, but I've given up on using
Word's calculation features for anything other than summing a column or
two when necessary. Even then, only if the content is going to remain
constant and not have to be updated periodically. I've been training
people to use Word for over 10 years including calcs in a table as well
as fields, & have been so frustrated that I typically recommend against
it. I'd much rather take my chances with OLE or even Copy & Paste.

Perhaps I just haven't worked at it a diligently as required, but I
don't need that capability with all the other options available.

Good Luck & sorry I wasn't more helpful |:>)

 

[diane]

Hey Taz!

Thanks for your good-natured reception, I misunderstood and thought

the
incorrect result was being had in the embedded wksht.

Oh, I'm definitely having troubles with the embedded worksheet as well,
that's what got me to asking about using Word's calculation features...


Thanks for writing back! You wrote "with all the other options
available" and I was wondering, what are the "other options"? Is it
embedding Excel worksheets that you're referring to? or mayhaps there's
something else I can do that'll allow me to accomplish my tasks?

I'm pretty sure I can give up on getting Word to do what I want it to
do, if you've been teaching and fighting this for 10 years then I'd
have to say that you're the expert and it's time I start looking for a
different solution, like getting embedded Excel worksheets to do what I
need them to do...

Sheesh, I've got so many tech issues running around in my head right
now that I can't even remember what the trouble was with Excel that
caused me to start looking to Word for a solution. I'll have to go
back and read my own posts...

I have found myself embroiled in another, bigger problem, last night
and this weekend... My Powerbook won't start up So, I'm on
my hubby's computer looking for help with my Powerbook and saw that you
had responded so I wanted to jump in real quick and tell you thank you
so very much for your help.

If you do have any more suggestions I'm all ears (and eyes and
willing to try just about anything you might suggest. Heck, right now
I'm looking at tossing this darn thing out the window, so any
suggestions you have may save this ailing computer of mine...

What do you tell your students who want to do these things, use an
embedded Excel worksheet? I'm on a Mac creating Word forms (and/or
Excel forms) for Windows and Mac users, just in case that might make a
difference in your response. Of course I can't do anything about
this issue until I can get my Powerbook started up but once it's up and
running. as our Governator would say, "I'll be back..."

Thank you again so much, I truly do appreciate your time and humor!

Have a great night, I'm off to post at another forum for help with my
dead Powerbook.
Diane

 

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

Hi Diane:

I'm with CyberTaz -- just don't do this in Word. Format the whole thing in
Excel.

Don't embed the Excel in word, put the words in Excel and print the whole
thing from Excel. That's how I do my invoices.

If you *had* to do it in Word, you can't reliably use a TABLE to do this
because Word's "smart functions" (smart ASS functions...) will get in the
way and helpfully give you the wrong answer.

Look up the formula Field in the Word help and have a play around with that.
It's a more reliable approach.

when you've finished fiddling, do it in Excel

Cheers

--

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