Calculations problem
in [Word VBA]
|
From: macropod on 22 Feb 2010 16:12 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. -- Cheers macropod [Microsoft MVP - Word] "mariel9898" <mariel9898(a)discussions.microsoft.com> wrote in message news:6F489780-8E42-48A7-81EB-EB7FEEE7B25C(a)microsoft.com... > 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
From: macropod on 22 Feb 2010 16:13 Oops! The formulae should be: {=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;} (note the change to the second formula). -- Cheers macropod [Microsoft MVP - Word] "mariel9898" <mariel9898(a)discussions.microsoft.com> wrote in message news:6F489780-8E42-48A7-81EB-EB7FEEE7B25C(a)microsoft.com... > 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
From: mariel9898 on 22 Feb 2010 18:09 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" wrote: > Oops! The formulae should be: > {=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;} > (note the change to the second formula). > > -- > Cheers > macropod > [Microsoft MVP - Word]
From: Peter Jamieson on 22 Feb 2010 19:08 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 On 22/02/2010 23:09, mariel9898 wrote: > 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" wrote: > >> Oops! The formulae should be: >> {=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;} >> (note the change to the second formula). >> >> -- >> Cheers >> macropod >> [Microsoft MVP - Word]
From: macropod on 22 Feb 2010 19:43 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] "mariel9898" <mariel9898(a)discussions.microsoft.com> wrote in message news:2683D431-5D9D-4C1A-8E22-E030001722D7(a)microsoft.com... > 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" wrote: > >> Oops! The formulae should be: >> {=INT(PRODUCT(LEFT)) \# $,0}{=MOD(PRODUCT(LEFT),1) \# .00;;} >> (note the change to the second formula). >> >> -- >> Cheers >> macropod >> [Microsoft MVP - Word]
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: problem in changing part of a sentence Next: Word Templates of the Same Name Problem |