Solved

How to setup calculating fields in Word 2007

Posted on 2011-03-06
23
577 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
  • 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
 

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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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
Ingeborg Hawighorst 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
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…

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now