Calculate text1 * text2 and output into text3

  • Thread starter OTWarrior via OfficeKB.com
  • Start date
O

OTWarrior via OfficeKB.com

I have collection of form fields, and I want 2 of them to do a calculation in
text3

Text 1 and text 2 are (numbered) editable text form fields, which the user
can put a number into, and text 3, is a calculation.

The fields are bookmarked as text1, text2 and text3

I have battled with this, and so far if i get text 3's calculation to be
"Text1" then it displays it, and the same with "text2". however, I want them
to be multiplied by each other and the result to be displayed in text 3.
like so:

text1: 2 text2: 5 text3: 10

However, when i put
=PRODUCT({Text1},{text2})
into the calculation box, all i get is "t"

text1:2 text2: 5 text3: t

I have tried other variations such as = product{text1,text2} to no prevail.

Am I not able to use bookmarks to do calculations here? as I have tried doing
the calculation in code (using a macro), but I cannot reference the bookmarks
in VB for some reason.

Any assistance would be much appreciated.
 
D

Doug Robbins - Word MVP

In the properties dialog for the formfield Text3, set the type of formfield
to Calculation and enter the formula = Text1 * Text2
Then in the properties dialog for each of Text1 and Text2, check the
Calculate on Exit box.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
O

OTWarrior via OfficeKB.com

That was the first thing I tried and it just says "text1 not in table" or it
comes up blank.

Thanks anyway.
In the properties dialog for the formfield Text3, set the type of formfield
to Calculation and enter the formula = Text1 * Text2
Then in the properties dialog for each of Text1 and Text2, check the
Calculate on Exit box.
I have collection of form fields, and I want 2 of them to do a calculation
in
[quoted text clipped - 29 lines]
Any assistance would be much appreciated.
 
M

macropod

Hi OTWarrior,

In general its better to avoid putting calculations in formfields - you can achieve the desired results with a formula field. In
this case, You could select the point where you want the output displayed and:
.. press Ctrl-F9 to create an empty field
.. type '=text1*text2' between the field braces, thus {=text1*text2}
.. press F9 to update the display.
With your text1 & text2 formfields, make sure the properties of both are set to 'calculate on exit'. The reason its better to avoid
putting calculations in formfields is that its easy to end up invoking bugs in the formfield calculations that give spurious
results.

Cheers
 
D

Doug Robbins - Word MVP

You need to check the bookmark name that is assigned to the formfield that
you think is Text1.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

OTWarrior via OfficeKB.com said:
That was the first thing I tried and it just says "text1 not in table" or
it
comes up blank.

Thanks anyway.
In the properties dialog for the formfield Text3, set the type of
formfield
to Calculation and enter the formula = Text1 * Text2
Then in the properties dialog for each of Text1 and Text2, check the
Calculate on Exit box.
I have collection of form fields, and I want 2 of them to do a
calculation
in
[quoted text clipped - 29 lines]
Any assistance would be much appreciated.
 
O

OTWarrior via OfficeKB.com

Thank you tremendously, problem solved :)

Although I do now have a second issue (now that that works). I want that
calculation to be used in another calculation, is this possible (ie:you the
result of the calculation in another calculation)

Also, I have drop down for fields with data in them, can these be used in a
calculation also.
eg:

calc1 =(list1*list2)

calc2 = (calc1*text3)

[hope that makes sense]
Hi OTWarrior,

In general its better to avoid putting calculations in formfields - you can achieve the desired results with a formula field. In
this case, You could select the point where you want the output displayed and:
. press Ctrl-F9 to create an empty field
. type '=text1*text2' between the field braces, thus {=text1*text2}
. press F9 to update the display.
With your text1 & text2 formfields, make sure the properties of both are set to 'calculate on exit'. The reason its better to avoid
putting calculations in formfields is that its easy to end up invoking bugs in the formfield calculations that give spurious
results.

Cheers
I have collection of form fields, and I want 2 of them to do a calculation in
text3
[quoted text clipped - 24 lines]
Any assistance would be much appreciated.
 
M

macropod

Hi Hi OTWarrior,

You could simply repeat the calculation at your other location. Alternatively, if you bookmark the first calculation you can use a
cross-reference to it.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

OTWarrior via OfficeKB.com said:
Thank you tremendously, problem solved :)

Although I do now have a second issue (now that that works). I want that
calculation to be used in another calculation, is this possible (ie:you the
result of the calculation in another calculation)

Also, I have drop down for fields with data in them, can these be used in a
calculation also.
eg:

calc1 =(list1*list2)

calc2 = (calc1*text3)

[hope that makes sense]
Hi OTWarrior,

In general its better to avoid putting calculations in formfields - you can achieve the desired results with a formula field. In
this case, You could select the point where you want the output displayed and:
. press Ctrl-F9 to create an empty field
. type '=text1*text2' between the field braces, thus {=text1*text2}
. press F9 to update the display.
With your text1 & text2 formfields, make sure the properties of both are set to 'calculate on exit'. The reason its better to
avoid
putting calculations in formfields is that its easy to end up invoking bugs in the formfield calculations that give spurious
results.

Cheers
I have collection of form fields, and I want 2 of them to do a calculation in
text3
[quoted text clipped - 24 lines]
Any assistance would be much appreciated.
 
D

Doug Robbins - Word MVP

You will not get the correct result if you use the result of one formfield
calculation as an input to another formfield calculation. To get around
this, in the second calculation, you must refer to the original textbox
formfields into which the user enters the data.

I would suggest that you use bookmark names for each formfield that reflect
the nature of the data that is being entered into them. With that approach,
if you had formfields for Quantity, Price, TotalbeforeDiscount,
DiscountPercent, DiscountAmount and DiscountedPrice, for
TotalbeforeDiscount, you would use =Quantity*Price; for DiscountAmount you
would use =Quantity*Price*DiscountPercent/100; and for DiscountedPrice you
would use =Quantity*Price*((1 - DiscountPercent)/100)

You will need to use a macro that runs on exit from each dropdown formfield
to get a result calculated on the basis of the item selected in the
dropdown. Such as

With ActiveDocument
.FormFields("text1").Result = .FormFields("Dropdown1").Result *
..FormFields("dropdown2").Result
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

OTWarrior via OfficeKB.com said:
Thank you tremendously, problem solved :)

Although I do now have a second issue (now that that works). I want that
calculation to be used in another calculation, is this possible (ie:you
the
result of the calculation in another calculation)

Also, I have drop down for fields with data in them, can these be used in
a
calculation also.
eg:

calc1 =(list1*list2)

calc2 = (calc1*text3)

[hope that makes sense]
Hi OTWarrior,

In general its better to avoid putting calculations in formfields - you
can achieve the desired results with a formula field. In
this case, You could select the point where you want the output displayed
and:
. press Ctrl-F9 to create an empty field
. type '=text1*text2' between the field braces, thus {=text1*text2}
. press F9 to update the display.
With your text1 & text2 formfields, make sure the properties of both are
set to 'calculate on exit'. The reason its better to avoid
putting calculations in formfields is that its easy to end up invoking
bugs in the formfield calculations that give spurious
results.

Cheers
I have collection of form fields, and I want 2 of them to do a
calculation in
text3
[quoted text clipped - 24 lines]
Any assistance would be much appreciated.
 
O

OTWarrior via OfficeKB.com

how would I cross reference it? the name is calc1, i have used that in the
way as said above. formula example:
[calc2] formula
=product(calc1,text3)

is there a different way of cross referencing? such as {ref {calc1}} [nb, i
have not got the ref statement to work yet whenever i tried it]

I'll try the repeat of the calculation though, hold it in ()?

Hi Hi OTWarrior,

You could simply repeat the calculation at your other location. Alternatively, if you bookmark the first calculation you can use a
cross-reference to it.

Cheers
Thank you tremendously, problem solved :)
[quoted text clipped - 30 lines]
 
O

OTWarrior via OfficeKB.com

That was perfecto, thank you so much.
I can stop pulling my hair out now ;)
You will not get the correct result if you use the result of one formfield
calculation as an input to another formfield calculation. To get around
this, in the second calculation, you must refer to the original textbox
formfields into which the user enters the data.

I would suggest that you use bookmark names for each formfield that reflect
the nature of the data that is being entered into them. With that approach,
if you had formfields for Quantity, Price, TotalbeforeDiscount,
DiscountPercent, DiscountAmount and DiscountedPrice, for
TotalbeforeDiscount, you would use =Quantity*Price; for DiscountAmount you
would use =Quantity*Price*DiscountPercent/100; and for DiscountedPrice you
would use =Quantity*Price*((1 - DiscountPercent)/100)

You will need to use a macro that runs on exit from each dropdown formfield
to get a result calculated on the basis of the item selected in the
dropdown. Such as

With ActiveDocument
.FormFields("text1").Result = .FormFields("Dropdown1").Result *
.FormFields("dropdown2").Result
End With
Thank you tremendously, problem solved :)
[quoted text clipped - 36 lines]
 
M

macropod

Hi OTWarrior,

To repeat the calculation {=text1*text2} or {PRODUCT(text1,text2)} at your other location, where you want to multiply the result by
text3, you could code it as:
{=text1*text2*text3} or {PRODUCT(text1,text2,text3)}
.. Alternatively, if you bookmark the first calculation as 'Calc1' you ccould use:
{=Calc1*text3} or {PRODUCT(Calc1,text3)}

Cheers


--
macropod
[MVP - Microsoft Word]
-------------------------

OTWarrior via OfficeKB.com said:
how would I cross reference it? the name is calc1, i have used that in the
way as said above. formula example:
[calc2] formula
=product(calc1,text3)

is there a different way of cross referencing? such as {ref {calc1}} [nb, i
have not got the ref statement to work yet whenever i tried it]

I'll try the repeat of the calculation though, hold it in ()?

Hi Hi OTWarrior,

You could simply repeat the calculation at your other location. Alternatively, if you bookmark the first calculation you can use a
cross-reference to it.

Cheers
Thank you tremendously, problem solved :)
[quoted text clipped - 30 lines]
Any assistance would be much appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top