• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

How to setup calculating fields in Word 2007

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
rayluvs
Asked:
rayluvs
  • 10
  • 7
  • 2
  • +4
5 Solutions
 
PlantwizCommented:
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
 
geff_changCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
stealth2549Commented:
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
 
10023Web site maintenance and designCommented:
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
 
PlantwizCommented:
@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
 
rayluvsAuthor Commented:
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
 
MINDSUPERBCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
last question.... can you explain:

why {=A2*D2 \# "$,0.00;negative; "} has the word "negative"?
0
 
rayluvsAuthor Commented:
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
 
PlantwizCommented:
@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
 
PlantwizCommented:
Disregard,  my negative is used differently and my screen was up and used to remove the discount value.

sorry
0
 
rayluvsAuthor Commented:
Can you please see what am I doing wrong on subtotal and the total?
(see file uploaded in ID: 35056104)
0
 
PlantwizCommented:
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
 
PlantwizCommented:
Make your Tax line:

{=sum(e4:e7)*.12}
0
 
PlantwizCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
PlantwizCommented:
@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
 
rayluvsAuthor Commented:
Thanx all
0
 
rayluvsAuthor Commented:
Thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 10
  • 7
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now