Calculations problem

[mariel9898]

I have a table that is part of a Word 2007 template. It has automatic
calculations set up where Column A times Column B is equal to a total in
Column C. Everything is in currency format (US dollars).

How can I get the totals to format with no zeros after the decimal point
when necessary and yet have denominations in cents when necessary?

Right now the totals can look something like this:

$2,500.00
$1,140.50
$950.50
$875.00

I want there to be no ".00" so it would look like this:

$2,500
$1,140.50
$950.50
$875

Just to throw a wrench in things, it would be totally useless if it the
numbers reverted to "$0.00" format when the documents print so it would have
to make sure it stays with no double zeros upon printing.

Also, how can get there to NOT be a space between the first number and the
dollar sign when the amount is less than $1000? Example: it comes out as $
550 rather than $550.

 

[Peter Jamieson]

If your calculation field is { =An*Bn } (where n is the number of the
row) you can try

{=int(An*Bn) \#$,0}{=mod(An*Bn,1) \#.00;.00;}

Peter Jamieson

http://tips.pjmsn.me.uk

 

[mariel9898]

My current code is this:

=PRODUCT(LEFT)

Numbe format is:
$#,##0.00;($#,##0.00)

I can't change the number format to anything on the standard drop down as
this would not be in currency and it does not show cents.

 

[Peter Jamieson]

1. OK, I don't think the suggestions I made will work if you try to use
form fields for your calculation results. Also, if either column A or B
contains anything except numbers, I don't think =PRODUCT(LEFT) will work
as you hope.

2. But for example, in column c, row 1, try the following:
a. press ctrl-F9 to insert a pair of the field code braces {}
b. click inside those and type
=int(product(left)) \#$,0

so you see

{=int(product(left)) \#$,0}

c. click to the right of the rightmost "}" and press ctrl-F9 again.
d. click inside the new braces and type
=mod(product(left),1) \#.00;.00;

so you see
{=mod(product(left),1) \#.00;.00;}

e. ensure that the properties for the form fields in column a and b are
set to "Calculate on Exit"
f. protect your form, and try again.

3. If that all works OK, try copying the two fields you created to the
other rows in column C.



Peter Jamieson

http://tips.pjmsn.me.uk

 

[mariel9898]

Didn't work. Something that was supposed to be $5,500.50 just showed up as
$5,500 (cents missing).

 

[macropod]

Hi Marie,

The results you're getting with {=int(product(left)) \#$,0} are exactly as they should be. For what you're after, you can use two
formula fields, coded as:
{=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT)) \# .00;;}
The first field shows just the whole dollar amounts, whilst the second field shows cents - but only if they're non-zero.

 

[macropod]

Oops! The formulae should be:
{=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;}
(note the change to the second formula).

 

[mariel9898]

Thank you, that worked but it won't be much use to me. That involves two
field codes, one of which is "invisble" (for lack of a better term). My end
users can barely deal with one field code.

Is there anything that can do this with one code that can be right clicked
and updated. Something super easy for the real basic user?

 

[Peter Jamieson]

The nearest you'll get is probably to nest both codes inside a { QUOTE }
field, e.g.

{ QUOTE {=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;} }

Something super easy for the real basic user?

Of course that's what we'd all like but "Word fields" and "super easy"
is a rare mix IME.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[macropod]

Hi Marie,

If the values in columns A & B are being input into text formfields, there's no need for anyone to concern themselves with selecting
the fields to update - checking the 'calculate on exit' formfield property will be enough.

 

[mariel9898]

Thanks but I got this error:

Error! Too many picture switches defined

 

[mariel9898]

Can you tell me where this 'calculate on exit' formfield property is found?

Also, I'm not sure that would work because the users need to see the actual
calculation and make sure it's correct. They can't just exit and assume
everything is right.

Hi Marie,

If the values in columns A & B are being input into text formfields, there's no need for anyone to concern themselves with selecting
the fields to update - checking the 'calculate on exit' formfield property will be enough.

--
Cheers
macropod
[Microsoft MVP - Word]

Thank you, that worked but it won't be much use to me. That involves two
field codes, one of which is "invisble" (for lack of a better term). My end
users can barely deal with one field code.

Is there anything that can do this with one code that can be right clicked
and updated. Something super easy for the real basic user?

.

 

[macropod]

Hi Marie,

Assuming you're using formfields, simply select one of them, right-click on it with the mouse, choose 'Properties' and a pop-up will
display showing various options, one of which is 'calculate on exit'.

As for your users being able to "see the actual calculation and make sure it's correct", I take it you're referring to being able to
see the result. They'll be able to see that as soon as they tab out of the formfield. If you mean you want them to be able to check
the underlying calculation, that won't be possible; in any event, your earlier statement that "My end users can barely deal with one
field code" suggests they really wouldn't be able to understand what they were looking at anyway if the field code was visible.


--
Cheers
macropod
[Microsoft MVP - Word]

Can you tell me where this 'calculate on exit' formfield property is found?

Also, I'm not sure that would work because the users need to see the actual
calculation and make sure it's correct. They can't just exit and assume
everything is right.

Hi Marie,

If the values in columns A & B are being input into text formfields, there's no need for anyone to concern themselves with
selecting
the fields to update - checking the 'calculate on exit' formfield property will be enough.

--
Cheers
macropod
[Microsoft MVP - Word]

Thank you, that worked but it won't be much use to me. That involves two
field codes, one of which is "invisble" (for lack of a better term). My end
users can barely deal with one field code.

Is there anything that can do this with one code that can be right clicked
and updated. Something super easy for the real basic user?


:

Oops! The formulae should be:
{=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;}
(note the change to the second formula).

.

 

[mariel9898]

I guess I'm not using a formfield because when I right click there is no
Properties option. The template is not a form. I just have calculated fields
in a table.

The field code does not need to be visible - just the end calculation. What
I mean is that the end user should just be able to change the numbers as
necessary then right click on the field to update it.

 

[Peter Jamieson]

It works fine here.

Are all the {} the field brace pairs that you can insert using ctrl-F9?

I am assuming that your default thousands separator is "," and your
decimal separator is "."

FWIW if you need to deal with negative numbers you need the format
switch I originally suggested, i.e.

{ QUOTE {=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;.00;} }

to cope with the


Peter Jamieson

http://tips.pjmsn.me.uk

 

[macropod]

Hi mariel9898,

In that case, embedding the two fields in a QUOTE field, as suggested by Peter, is probably the best way to go. I realize you've had
some difficulties getting this to work but, once coded correctly (as per Peter's example), it will do as you need it to.