Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to setup calculating fields in Word 2007

Posted on 2011-03-06
Medium Priority
587 Views
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
Question by:rayluvs
• 10
• 7
• 2
• +4

LVL 11

Expert Comment

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

ID: 35052676
0

LVL 1

Assisted Solution

geff_chang earned 400 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

Assisted Solution

stealth2549 earned 400 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

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

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

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

Plantwiz earned 400 total points
ID: 35053476
0

LVL 19

Expert Comment

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
``````
InvoiceIncludeCalcuation-1-.docx
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 800 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

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

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

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

Author Comment

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

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

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

sorry
0

Author Comment

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

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

ID: 35058425

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

LVL 11

Expert Comment

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 800 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

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

ID: 35069469
Thanx all
0

Author Closing Comment

ID: 35070008
Thanx
0

## Featured Post

Question has a verified solution.

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

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to tâ€¦
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to refâ€¦
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Persoâ€¦
###### Suggested Courses
Course of the Month6 days, left to enroll