Solved

How to setup calculating fields in Word 2007

Posted on 2011-03-06
23
583 Views
Last Modified: 2012-05-11
Included is a Word document we're trying to include calculation.  

Its a simple invoice form where the Total column is the multiplication of Qty and :Price.  The bottom part is a summary with a subtracting the discount.

Whats the best way to setup calculated field in Word so I don't have t use a calculator.
InvoiceIncludeCalcuation.doc
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 2
  • +4
23 Comments
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35052635
Which version of Word are you using?

You should be able to add a forumla to the cell you created, but if you can help me with your version, I can better assist in the 'how-to'
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35052676
0
 
LVL 1

Assisted Solution

by:geff_chang
geff_chang earned 100 total points
ID: 35052752
It's difficult to have calculations in Word, but not impossible.
Check this link if you really need to do it:
http://office.microsoft.com/en-us/word-help/perform-calculations-in-a-table-HP005256736.aspx
Take note that you have to manually refresh each value.

BUT, I suggest you use Excel instead.
Calculation in Word is too much trouble.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Assisted Solution

by:stealth2549
stealth2549 earned 100 total points
ID: 35052816
If using Word 07, click on the layout tab and it will bring up a formula button on the far right.  This can then be filled out similarly to an excel table cell. I.e. =sum(e2:e5) will complete the calculation for your ex tax amount in the sample given.
0
 
LVL 10

Expert Comment

by:10023
ID: 35053273
I agree with geff_chang.  Build your calculations in excel...and then paste the excell cells into word as a linked document (you can choose to keep formatting)...The word file then updates the calculations automatically when excel is edited.
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35053301
@Ramante

If you are going to build your formula's in Excel, why not just make your Invoice Template in Excel and and use Excel, skip Word?

(sorry, was attempting to post but EE was down :(  )
0
 

Author Comment

by:rayluvs
ID: 35053400
My version is 2007, the links refer to 2003

I know we can use Excel, but the template we use is a lot of work to start in Excel at this poinmt.  Se we would like insert calculations.
0
 
LVL 11

Assisted Solution

by:Plantwiz
Plantwiz earned 100 total points
ID: 35053476
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35054270
Ramante,

Open the attached file and see if this what you want. This is only a sample and you can get idea from it.

Note: You need to copy and paste the codes below into VBA whithin "ThisDocument".

Sincerely,
Ed
Private Sub Price1_Change()
Me.Total1 = Me.Qty1 * Me.Price1
End Sub

Private Sub Price2_Change()
Me.Total2 = Me.Qty2 * Me.Price2
End Sub

Private Sub Qty1_Change()
Me.Total1 = Me.Qty1 * Me.Price1
End Sub

Private Sub Qty2_Change()
Me.Total2 = Me.Qty2 * Me.Price2
End Sub



Private Sub SubTotal_Change()
Dim intSubTotal As Integer
Dim intTax As Integer
Dim intMisc As Integer
Dim intFreight As Integer
Dim intDiscount As Integer
intSubTotal = Me.SubTotal.Value
intTax = Me.Tax
intMisc = Me.Misc
intFreight = Me.Freight
intDiscount = Me.Discount
Me.Total = (intSubTotal + intTax + intMisc + intFreight) - intDiscount
End Sub

Private Sub Total1_Change()
Me.SubTotal = Me.Total1 + Me.Total2
End Sub

Private Sub Total2_Change()
Dim intTotal1 As Integer
Dim intTotal2 As Integer
intTotal1 = Me.Total1.Value
intTotal2 = Me.Total2.Value
Me.SubTotal = intTotal1 + intTotal2
Me.Tax = Me.SubTotal * 0.12
End Sub

Open in new window

InvoiceIncludeCalcuation-1-.docx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 200 total points
ID: 35054430
Hello,

you can do these calculations in Word natively with field codes. There is certainly no need for VBA to achieve that, and you also do not need Excel for simple calculations like this.  You should align your table cells, though, so the Subtotal (labels) and cells below are in the same grid. Otherwise, Word will not be able to figure out where things are.

See attached sample. I've re-arranged the table to align the Subtotal and following cell labels with the table, and applied no border for the cells left of Subtotal. The line totals have been pre-filled with the formulas, and they are formatted to suppress zero values if there are no entries in the first 10 rows of the table.

Mind you, that if you insert new rows between the second and 10th row, the formula will not automatically be copied into the new row. You will need to either set up a template with sufficient rows and pre-populate the cells, or add the field code with the formula on every new row you may want to insert into the table.

Hit Alt-F9 to see the field codes.

The calculations for the first 10 rows are pre-filled and formatted not to show zeros.

When you enter new values into the first 10 rows of the table, you need to select the table and hit F9 to refresh.

The Tax value is calculated. Misc, Freight and Discount are values that are entered manually by the user, as I understand your scenario. The total is calculated by a field code as well.

Word calculations are done on a A1 addressing basis. The first cell in the table is A1. The rest of the addresses works like in Excel. If you have rows with split or merged cells, or cells deleted and the remainder shifted to the right, the concept falls over. If you want to keep the calculation in Word, then you need the table as one consistent structure.

Use Alt-F9 to explore the formulas and come back here if you have questions.

cheers, teylyn


InvoiceIncludeCalcuation.doc
0
 

Author Comment

by:rayluvs
ID: 35055917
Hey teylyn, thats exactly it!  Thanx lots!  we had it but the only thing missing was subtotal, but I saw how you did it thanx!
0
 

Author Comment

by:rayluvs
ID: 35055948
last question.... can you explain:

why {=A2*D2 \# "$,0.00;negative; "} has the word "negative"?
0
 

Author Comment

by:rayluvs
ID: 35056104
also I transfered your calcs to my forms (similar) and the subtotal and the total is giving an error:

!E8 Is Not In Table

Can you please see what am I doing wrong?
InvoiceIncludeCalcuation.doc
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35056605
@ramante

>why {=A2*D2 \# "$,0.00;negative; "} has the word "negative"?

'negative' is gonig to give you the effect of subtraction.

I had this set up but the site went down last night and couldn't post.  However, teylyn seems to have you on track. :)
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35056652
Disregard,  my negative is used differently and my screen was up and used to remove the discount value.

sorry
0
 

Author Comment

by:rayluvs
ID: 35057586
Can you please see what am I doing wrong on subtotal and the total?
(see file uploaded in ID: 35056104)
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35058392
Looking at it.

I believe you may find this helpful and i"m testng the formula right now:
http://www.brainbell.com/tutorials/ms-office/Word/Create_Formulas_In_Tables.htm

Because you are outside of excel at this point, you'll need to use the ABOVE and BELOW commands along with Row and column to target the correct numbers to total

0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35058425
Make your Tax line:

{=sum(e4:e7)*.12}
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35058738
OK... I need to walk away.

I'm not successful in modifying your .doc
Yet I can create a new one and it works...

....grrrr

I'll put clean eyes on it in a bit, sorry about the delay
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 200 total points
ID: 35061250
Hello Ramante,

re this:

>> also I transfered your calcs to my forms (similar) and the subtotal and the total is giving an error:

!E8 Is Not In Table

Can you please see what am I doing wrong?

I wrote in my first comment: You should align your table cells, though, so the Subtotal (labels) and cells below are in the same grid. Otherwise, Word will not be able to figure out where things are.

You do not have the subtotal in the same grid. Compare your table to the table in the sample file I attached. I have one contiguous table with no column width changes. I only made the cells to the left of Subtotal, etc. invisible by removing their borders. Your version has only two columns from Subtotal down and Word can not relate to these cells with the A1 style addressing.

What you need to do:
- insert 6 rows after the line items. Full rows, with all columns, so the current Subtotal and cells below move down.
- copy the Subtotals labels into the new rows into column "D"
- copy the fields into column "E"
- delete your old subtotal etc, rows.


The formatting switch \# "$,0.00;negative; " is used to suppress zeros in rows with no data, so you don't see a 0 as a result of multiplying two empty cells.

Hope that helps.

cheers, teylyn
0
 
LVL 11

Expert Comment

by:Plantwiz
ID: 35061630
@teyln:

>I wrote in my first comment: You should align your table cells, though, so the Subtotal (labels) and cells below are in the same grid. Otherwise, Word will not be able to figure out where things are

GOOD ONE!

exactly why I can recreate a new document but not modify his...

Good Support!
0
 

Author Comment

by:rayluvs
ID: 35069469
Thanx all
0
 

Author Closing Comment

by:rayluvs
ID: 35070008
Thanx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question