Solved

How to setup calculating fields in Word 2007

Posted on 2011-03-06
23
575 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:
teylyn 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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:teylyn
teylyn 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
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.

757 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

18 Experts available now in Live!

Get 1:1 Help Now