Hampered by MS word's field code limits 1

[dipstik]

Hi all,

I am trying to round mid-way numbers to the closest even numbers with a certain precision for three seperate ranges:
0-50k,50 to 100k and over 100k with precisions 100, 500 and 1000 respectivly (round 1.5 to 2 and 2.5 to 2 and for non decimal rounding multiply the value, apply rounding procedure and then divide the result). an example would be 75250 would be multiplied by 2 to get 150500, then round using 1000 place to nearest even thousand, giving 150000, then divide by 2 to get 75000. but 75750 would give 76000 based on the same procedure (rounding to nearest thousand after doubling)

I have written a function that does this as follows (please provide a simpler one if you may)

=IF(B8/B6>=100000,IF(ABS(B8/B6-FLOOR(B8/B6,1000))<>500,(ROUND(B8/B6,-3)),IF
(ISEVEN((FLOOR(B8/B6,1000)/1000)),FLOOR(B8/B6,1000),FLOOR(B8/B6,1000)+1000)
),IF(AND(B8/B6<100000,B8/B6>=50000),IF(ABS(B8/B6-FLOOR(B8/B6,500))<>250,(RO
UND(2*B8/B6,-3)/2),IF(ISEVEN((FLOOR(2*B8/B6,1000)/1000)),FLOOR(B8/B6,500),F
LOOR(B8/B6,500)+500)),IF(ABS(B8/B6-FLOOR(B8/B6,100))<>50,ROUND(B8/B6,-2),IF
(ISEVEN((FLOOR(B8/B6,100)/100)),FLOOR(B8/B6,100),FLOOR(B8/B6,100)+100))))

I wrote the code in excel and hoped it would work in word, as has been the case many times before with simple IF statements. I am trying to use an insert field calculation in word 2000, and all the text is there but i get the "syntax error (" remark, but i get a value from excel with the same formula(it is not a data reference error). When I tried using a form field I was limited by the number of characters. I cant get it to work in word

Can someone please advise. I would like to avoid coding anything, since I don't know vb at all.

 

[macropod]

Hi dipstik,

Word's field functions don't include support for FLOOR or ISEVEN

However, for both Word and Excel, you could use a formula coded as:

=IF(B8/B6<50000,ROUND(B8/B6,-2),IF(B8/B6<100000,ROUND(B8*2/B6,-3)/2,ROUND(B8/B6,-3)))

In Word, you could use an ordinary formula field for this rather than a form field. For the formula field approach, simply press Ctrl-F9 to create a pair of field braces, then paste the equation into it. If you need to refer to the result of this calculation elsewhere in the document, simply bookmark it and use a cross-reference to the bookmark.

Cheers

[MS MVP - Word]

 

[dipstik]

that would not address the rounding to evens protocol. 1650 would be rounded to 1700 instead of 1600. and 60250 would give me 60500 instead of 60000.

is there any way to add these functions to word? a plugin or add-in or something?

i can try to use the script editor if someone sees a solution that way...

thanks

 

[macropod]

Hi dipstik,

Are you sure your Excel formula is working correctly?

According to my testing, your Excel formula does the following roundings:
1649 > 1600
1650 > 1600
1651 > 1700
1749 > 1700
1750 > 1800
1751 > 1800

Ordinarily, one would expect:
1649 > 1600
1650 > 1700
1651 > 1700
1749 > 1700
1750 > 1800
1751 > 1800
which is what my formula gives.

The only difference between the two examples above is the treatment of 1650. Is it your intention that 1650 be rounded down whilst 1750 is rounded up?

Cheers

[MS MVP - Word]

 

[dipstik]

yes, that is my intention. the standard states that all middle values must be rounded to the nearest even number. if you are rounding to the nearest 100, then 150 would go to 200 as would 250. when you have to round to the nearest non-decimal figure, such as nearest 500, then you must multiply the value by 2, then round using the convention above and then divide the result by 2 again. such as 55250, times 2, is 110500, which rounds to 110000, which goes to 55000, but 55750 goes up to 56000, instead of 55500. the weirdest one is when a percentage has to be rounded to the 0.2%. you have to multiply by 5, then round to the 1's place (in percent, hundredths place normally).

thanks for all the help

 

[macropod]

Hi dipstik,

Then try this in both Word and Excel:

=IF(B8/B6<50000,ROUND(B8/B6,-2)-(MOD(B8/B6,50)=0)*(MOD(B8/B6,200)<>0)*100,IF(B8/B6<100000,ROUND(B8*2/B6,-3)/2-(MOD(B8/B6,250)=0)*(MOD(B8/B6,1000)<>0)*500,ROUND(B8/B6-(MOD(B8/B6,500)=0)*(MOD(B8/B6,1000)<>0)*1000,-3)))

In Word, though, use a formula field (which you create via Ctrl-F9) rather than a form field. Then, if you need to refer to the calculated result elsewhere in your document, bookmark the formula field (or the whole table) and use the appropriate cross-referncing technique (see my Word Field Maths 'tutorial', at:

http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442

or

http://www.gmayor.com/downloads.htm#Third_party).

Cheers

[MS MVP - Word]

 

[dipstik]

looks like we are getting closer, but I am getting 49100 as a return for 49150. I should be getting 49200. I would also appreciate any explaination of the boolean application of the mod function, since i would think that mod(x,y) would just return the remainder of x/y, but i see the formula has some tautological statements appended to mod(), such as mod()<>0 etc.

if I can get an explanation I might be able to use this info in the future.

thanks

 

[macropod]

Hi dipstik,

This should do it in both Word and Excel:

=IF(B8/B6<50000,ROUND(B8/B6,-2)-(MOD(B8/B6,50)=0)*(MOD(ROUND(B8/B6,-2),200)<>0)*100,IF(B8/B6<100000,ROUND(B8*2/B6,-3)/2-(MOD(B8/B6,250)=0)*(MOD(ROUND(B8*2/B6,-3)/2,1000)<>0)*500,ROUND(B8/B6,-3)-(MOD(B8/B6,500)=0)*(MOD(ROUND(B8/B6,-3),2000)<>0)*1000))

What I've done here is to apply the second MOD test to the normally-rounded value instead of to the unrounded value.

As for how MOD tests, the MOD tests are used, you'll appreciate that =MOD(150,100) returns 50, whilst =MOD(200,100) returns 0. Now, if you add '=0' to both these formulae, Word and Excel will both return 'FALSE' and 'TRUE', respectively. When multiplied by the results of another expression, 'FALSE' and 'TRUE', function as 0 and 1, respectively (eg =(MOD(150,100)=0)*1 returns 0 and =(MOD(150,100)=50)*1 returns 1). Similarly if you multiply two 'FALSE' results, you end up with 0*0=0, whilst with 'FALSE' and 'TRUE' results, you end up with 0*1=0 and with two 'TRUE' results, you end up with 1*1=1. You can apply the same technique with other formulae, numbers and text strings.

I used the MOD tests to determine whether the rounded value was 'odd'. I then simply multiplied the 1 or 0 result of this test by the required 'evens' adjustment and deducted the product from the normally-rounded value.

One could get the same results using more IF tests instead of using the TRUE/FALSE equations and multiplying the TRUE/FALSE results, but this way is simpler.

Cheers

[MS MVP - Word]

 

[dipstik]

elegant and functional.

thank you very much.

 

[macropod]

Hi dipstik,

Correction:

=IF(B8/B6<50000,ROUND(B8/B6-(MOD(B8/B6,50)=0)*(MOD(ROUND(B8/B6,-2),200)<>0),-2),IF(B8/B6<100000,ROUND(B8*2/B6-(MOD(B8/B6,250)=0)*(MOD(ROUND(B8*2/B6,-3/2),500)<>0),-3)/2,ROUND(B8/B6-(MOD(B8/B6,500)=0)*(MOD(ROUND(B8/B6,-3),2000)=0),-3)))

This formula is simpler too.

Cheers

[MS MVP - Word]

 

[macropod]

Correction 2 (spot the typos):

=IF(B8/B6<50000,ROUND(B8/B6-(MOD(B8/B6,50)=0)*(MOD(ROUND(B8/B6,-2),200)<>0),-2),IF(B8/B6<100000,ROUND(B8*2/B6-(MOD(B8/B6,250)=0)*(MOD(ROUND(B8*2/B6,-3)/2,1000)<>0),-3)/2,ROUND(B8/B6-(MOD(B8/B6,500)=0)*(MOD(ROUND(B8/B6,-3),2000)<>0),-3)))

Cheers

[MS MVP - Word]

 

[dipstik]

for some reason i am getting a "!syntax error, )".

the formula works fine in excel, and the entire formula will fit in a form field, but a form field will not spit out any result at all. I used the formula field as you suggested and the error above is the result for some reason. I would think word could perform a calc with 236 characters. I have tried in word 2000 and 2003 with no luck.

I have been able to use the first if statements with the mod arguments in the round function, but once i try to include the second if statement it results in an error. I don't think i've ever had this much trouble with a function before.

 

[macropod]

Hi dipstik,

In Word, try the formula coded as:

{=IF(B8/B6<50000,ROUND(B8/B6-(MOD(B8/B6,50)=0)*(MOD(ROUND(B8/B6,-2),200)<>0),-2),IF(B8/B6<100000,ROUND(B8*2/B6-(MOD(B8/B6,250)=0)*[red][b]{[/b][/red]=MOD([red][b]{[/b][/red]=ROUND(B8*2/B6,-3)/2[red][b]}[/b][/red],1000)<>0[red][b]}[/b][/red],-3)/2,ROUND(B8/B6-(MOD(B8/B6,500)=0)*(MOD(ROUND(B8/B6,-3),2000)<>0),-3)))}

where the '[red]{[/red]' and '[red]}[/red]' are pairs of field braces created via Ctrl-F9. Note that the '[red]{[/red]' are followed by an '=' and that I've removed the brackets surrounding the affected MOD test.

Cheers

[MS MVP - Word]

 

[macropod]

Hi dipstik,

Even that gives problems for values over 100,000. Try coding the field as:

[red][b]{[/b][/red]=IF(B8/B6<50000,ROUND(B8/B6-(MOD(B8/B6,50)=0)*(MOD(ROUND(B8/B6,-2),200)<>0),-2),[red][b]{[/b][/red]=IF(B8/B6<100000,[red][b]{[/b][/red]=ROUND(B8*2/B6-(MOD(B8/B6,250)=0)*[red][b]{[/b][/red]=MOD([red][b]{[/b][/red]=ROUND(B8*2/B6,-3)/2[red][b]}[/b][/red],1000)<>0[red][b]}[/b][/red],-3)/2},[red][b]{[/b][/red]=(ROUND(B8/B6-(MOD(B8/B6,500)=0)*(MOD(ROUND(B8/B6,-3),2000)<>0),-3))[red][b]}[/b][/red])[red][b]}[/b][/red])[red][b]}[/b][/red]

In effect, I've turned each of the True/False responses for the IF tests into their own fields.

Cheers

[MS MVP - Word]

 

[dipstik]

perfect.

now i need something to round to the nearest 0.2 percent...

im trying

=IF(MOD(500*B19/8,1)=0.5,(ROUND(500*B19/8,0)/5)-0.2*(MOD(500*B19/8,2)<>0))

and

=100*ROUND(B19*5/8-(MOD(B19/8,0.004)=0)*(MOD(ROUND(B19*5/8,0)/5,0.016)<>0),2)/5

but i cant get the alternate rounding to work out for stuff like 1/8 versus 1.4/8 and the other 0.1% resolutions...

i'm going to try some more on monday.

i got the other one to work by embedding those field codes... took me a while since i didn't know you couln't just type { }, but have to press F9

 

[macropod]

Hi dipstik,

OK, if you run into trouble, post back - perhaps in a new thread. You'll probably find it easiest the develop the formulae in Excel, then make whatever minor modifications might be necessary to get them working as fields in Word.

Cheers
PS: As mentioned in my first post on the 10th, its Ctrl-F9 to create a field - F9 just updates it. Shift-F9 can be used to toggle the code display for an individual field, whilst Alt-F9 does it for the whole document.

[MS MVP - Word]

 

[macropod]

Hi dipstik,

Regarding the rounding to the nearest 0.2%, do you need anything more sophisticated than

=ROUND(B19/2,3)*2

Cheers

[MS MVP - Word]

 

[dipstik]

Yeah, i need the even rounding scheme...

i'm having some weird problems in excel though...

i have a mod test in an if statement like so:

So, I started with this for stuff under 10%:
IF(MOD(2*B2,0.02)=0.005,ROUND(2*B2-0.000001,2)/2,ROUND(2*B2,2)/2)

and this for stuff over 10%:
IF(MOD(B4,0.02)=0.005,ROUND(B4-0.000001,2),ROUND(B4,2))

but: It thinks a quarter of the times that the mod tests should be true are false, so i decided to try to do something that would vary through values every 4 times instead. So I used 0.008 because it will cycle every 4 values.

I solved the problem by making sure no divisor or mod-equal-to argument was less than 1, so:

MOD(B4,0.02)=0.005 becomes
MOD(1000*B4,20)=5

which seems to work. my rounding scheme is far uglier than yours, but it was too difficult for me top code yours into word for some reason. I kept trying but i dont know if it was the commas or the spaces i was messing up, it wouldn't give me the answer.

thanks for all the help.

 

[dipstik]

acually, i had to change the mod-equal-to value to something completely different. my main goal was to find something that would go between two numbers when checking the critical values, but when i multiply everything by the same order or 10 i end up with similar mod values for all critical values. most interesting was that the errors of the mod test only occured in the 10% and over range, even though i had made no distinction in the formula... and all of the other formulas like this that work do not have mod divisors or equal-to values less than 1.

i had to change the formula to this:

IF(B2<0.1,IF(MOD(2000*B2,20)=5,ROUND(2*B2-0.000001,2)/2,ROUND(2*B2,2)/2),IF(MOD(1000*B2,4)=1,ROUND(B2-0.000001,2),ROUND(B2,2)))