If field calculation

[Guest]

I am trying to insert an if calculation into a Word table. It will reference
two other fields in other locations in the table. One is a number of pages
(let's call that "Total"). Another is a rate ("Rate"). We think we have the
math figured out, but I don't have enough experience with IF and calculation
fields to figure this out very easily.

We also think it will use the "round" function, which we think means if a
number in our calculation is a decimal, it will be rounded up to the nearest
whole number.

IF the Total>100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 

[Charles Kenyon]

{ IF { REF "Total" } > 100 "{ = ROUND({ = { = { = { REF "Total" }-100 }
/50 } * { REF "Rate" } },0) }" "0" }

I think. I may have overdone it but it seems to work.
--

Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://www.mvps.org/word which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[Greg Maxey]

Dawn,

This one is not as easy as you might expect. This is due in part to the
fact that Word has no Round Up function.

I didn't have time to refine this but it will work. For the purpose of this
example, lets put Total in cell a1 and put Rate in Cell b1

The formula
{ IF a1 >"100""{
=round((((a1-100)/50*b1)+(MOD(((a1-100)/50*b1),1)<>0)/2),0) }""0"}

should work. Note the field braces { } must be entered with CTRL+F9

 

[Guest]

Wow, thanks! I can see it would have taken about an ice age for me to figure
this out on my own. The formula works perfectly as long as I enter a total
that is a multiple of 50. But something doesn't seem to work quite right, I
think it's the "round" feature. When I entered 201 for the "total" and a
rate of "200"I got a resulting amount of $404. The answer I want it to give
me is $400. 201-100/50 = 2.02. I need 2.02 to round up to 3 and then have 3
multiply by the "rate". Any further ideas? Thanks so much for your help!

 

[Greg Maxey]

Dawn,

In that case use:

{ IF a1 >"100""{ =(
round((((a1-100)/50)+(MOD(((a1-100)/50),1)<>0)/2),0)*b1)}""0"}

 

[Greg Maxey]

Dawn (and Charles too if your are around)

As I mentioned earlier, Word does not have a round up function. Below is a
similified version of the of a formual similiar to the one Charles provided.
This formula would round the 2.02 down to 2 and the result would be 400. In
order to round up, you have to do a little more math

{ IF a1 > 100 "{ =(round(((a1-100)/50),0)*b1) }""0" }

or

{ IF a1 >"100""{
=(round((((a1-100)/50)+(MOD(((a1-100)/50),1)<>0)/2),0)*b1)}""0"}

 

[Guest]

Hmmm... yep, rounding definitely seems to be the culprit. Do you know what
exactly the "round" feature does? A fellow here thinks he can determine
mathmatically what we need to do, regardless of how the rounding actually
works, as long he knows what the rounding will actually do.

When I tried your IF field, I got a result of $100, when I entered a total
pages of 201, with a rate of 200. (Is that what you get, or have I entered
the calculation incorrectly?) That isn't right. What it should be is $400.
I probably haven't explained the math very clearly. For every 50 pages over
100, there is a fee of $200. So, 0-100 pages gets $0 fee. 101-150 pages get
a $200 fee. 151-200 pages gets a $400 fee. 201-250 gets a $600 fee, etc.

Thanks again for any help you can offer!

 

[Greg Maxey]

Dawn,

Our posts are crossing. The original formula I provided returns 404. You
must have made a typo somewhere.

Then you said that for the value of 201 and 200 you want to round up the
2.02 to 3 so the result should be 600. The second formula I provided will
round up (it is a combination round and mod forumal) 2.02 to to and return
600 for values Total 201 and Rate 200. Now you are saying that it should be
400. Which is it??

Here is the formula to return 600 for values Total 201 and Rate 200: Note
the forula is broken by text wrapping so bring the =round back up to the
same line.

{ IF a1 >"100""{
=(round((((a1-100)/50)+(MOD(((a1-100)/50),1)<>0)/2),0)*b1)}""0"}

 

[Greg Maxey]

Dawn,

After reading your pricing scheme a little closer I don't thing I would use
a round/mod formula.


Try

{ If { = ((a1-100)/50) }>{ =int((a1-100)/50) }"{
(int(((a1-100)/50)+1)*b1) }""0"}

 

[Guest]

Ugh, sorry. The result should be $600 (with a total pages of 201 and a rate
of 200). The part where I wrote out the several examples trying to explain
the calculation is correct. Sorry about that.

I'll have to try the calculation again in the morning, I've looked at it
several times and re-entered it from scratch twice and can't find where my
mistake is. Turns out I get the value 100 no matter what I input I use for
total pages or the rate. I had deleted the line break, as you noted, and I
am entering the braces with control-F9, so that's not it.

Can you describe what the "round" function actually does? Is it round up
for decimals .5 and higher and round down for .4 and lower or something?

Thanks again for your help!

 

[Greg Maxey]

Dawn,

$600.00 I thought so.

If you are still messing with the first formula I sent you then stop. I
wrote that before I knew that you want 2.02 rounded up to 3. The first
formula I sent would return 404 which is what you don't want.

Here is a round/mod formula that will return 600 or inputs Total 201 and
Rate 200. I have haven't check if it is accurate across the range of your
pricing scheme:

{ IF a1 >"100""{
=(round((((a1-100)/50)+(MOD(((a1-100)/50),1)<>0)/2),0)*b1)}""0"}

As I mentioned in a previous post, I would probably use this instead:

{ If { = ((a1-100)/50) }>{
int((a1-100)/50) }"{(int(((a1-100)/50)+1)*b1) }""0"}


Yes a round formula {=round(X,0) would round 2.4999999999999 and below to 2
and 2.5 and above to 3.

 

[Greg Maxey]

Dawn,

Typing field codes can be difficult. I used a macro that converts field
codes to a string of text, but in looking at my last post something did'nt
convert correctly. I also realized that the last equation I gave you using
the INT function would product an erroneous result in situations where the
page numbers where exact multiples of 50. I think that I have that worked
out now and I am going to provide the equation here typed out by hand.
Hopefully I get it right.

{IF{=((a1-101)/50)}>={=int((a1-101/50)}{=((int((a1-101)/50)+1)*b1)}"0"}

Here is an example of how this equation works. First lets use your values
201 and 200.

The part {=((a1-101)/50)} returns 2
The part {=int((a1-101/50)} returns the interger value of the formula
int(x). Again in this case 2

IF "2" >= 2 and it is, Then take the int(x) value +1 and mulitply by b1
Or
If "2" >+ 2 Then return (2+1)*200 and the result is 600


Now lets look a 175 and 200. The desired result is 400
The part {=((a1-101)/50)} returns 1.48
The part {=int((a1-101/50)} returns the interger value of the formula
int(x). In the case the integer value of 1.48 or 1

So

If 1.48 >= 1 and it is, Then take take the int(x) value +1 and mulitply by
b1
Or
If "2" >+ 2 Then return (1+1)*200 and the result is 400


BTW, the round/mod equation will work equally as well:
{ IF a1 >"100""{ =(
round((((a1-100)/50)+(MOD(((a1-100)/50),1)<>0)/2),0)*b1)}""0"}

 

[Charles Kenyon]

Rounding is supposed to get you to the (approximately) closest digit. So
..0549 will round to .05 while .055 will round to .06 if you are specifying 2
decimal places for rounding. Take a look at Greg's last formula which
doesn't use the Round function at all.
--

Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://www.mvps.org/word which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[Guest]

Thanks again for your help Greg and Charles! I still don't know what the
problem was, but I had difficulty getting any of Greg's formulas to work at
all. I was probably doing something wrong. I also lost my internet
connection for about a day so couldn't access the newsgroup to ask for more
help. What bad timing! I was able to find someone here at work who came up
with a way of accomplishing our goal, which I'm posting in case anyone else
is interested in this thread.

We used a field for the user to type in the total pages (bookmarked as
"totalpages", and the number 100 in a second column (bookmarked "pagesfree").
This was mostly for user-friendliness, so they could see what numbers were
being used. Then he was able to make a field that subtracted pagesfree from
the total pages, divided that total by 50. Now here's the tricky part: he
added 0.49 to the result and rounded the resulting number. Adding the 0.49
has the effect of making the "round" function into a "round up" function. It
works! Sneaky.

Here's the field we used:

{=MAX(ROUND(0.49+((totalpages-pagesfree/50),0),0)\*MERGEFORMAT}

Thanks again for the time you spent helping me, I appreciate it. Hope this
might help anyone else with this issue.

Thanks,
Dawn