Prev: problem in changing part of a sentence
Next: Word Templates of the Same Name Problem
From: mariel9898 on 22 Feb 2010 11:11
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.

From: Peter Jamieson on 22 Feb 2010 12:35
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

On 22/02/2010 16:11, mariel9898 wrote:
> 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.
>
From: mariel9898 on 22 Feb 2010 13:57
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.
From: Peter Jamieson on 22 Feb 2010 14:56
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

On 22/02/2010 18:57, mariel9898 wrote:
> 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.
From: mariel9898 on 22 Feb 2010 16:01
Didn't work. Something that was supposed to be $5,500.50 just showed up as
$5,500 (cents missing).

"Peter Jamieson" wrote:

> 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
 |  Next  |  Last
Pages: 1 2 3 4
Prev: problem in changing part of a sentence
Next: Word Templates of the Same Name Problem