Remember Me? If field calculation

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!
 Charles Kenyon Posts: n/a { 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

--------- --------- --------- --------- --------- ---------
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.

"Dawn Rhoads" Dawn wrote in message
...
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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a 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
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks! 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!

"Charles Kenyon" wrote:

{ 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

--------- --------- --------- --------- --------- ---------
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.

"Dawn Rhoads" Dawn wrote in message
...
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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a Dawn,

In that case use:

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

--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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!

"Charles Kenyon" wrote:

{ 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

--------- --------- --------- --------- --------- ---------
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.

message ...
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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a 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"}

--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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!

"Charles Kenyon" wrote:

{ 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

--------- --------- --------- --------- --------- ---------
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.

message ...
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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks! 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.

"Greg Maxey" wrote:

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
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a 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/Word MVP
A Peer in Peer to Peer Support

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.

"Greg Maxey" wrote:

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
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a 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"}

--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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.

"Greg Maxey" wrote:

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
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks! 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?

"Greg Maxey" wrote:

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/Word MVP
A Peer in Peer to Peer Support

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.

"Greg Maxey" wrote:

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
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a 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/Word MVP
A Peer in Peer to Peer Support

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?

"Greg Maxey" wrote:

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/Word MVP
A Peer in Peer to Peer Support

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.

"Greg Maxey" wrote:

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 --
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Greg Maxey Posts: n/a 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"}

--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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?

"Greg Maxey" wrote:

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/Word MVP
A Peer in Peer to Peer Support

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.

"Greg Maxey" wrote:

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 --
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks!

 Charles Kenyon Posts: n/a 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

--------- --------- --------- --------- --------- ---------
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.

"Dawn Rhoads" Dawn wrote in message
...
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.

"Greg Maxey" wrote:

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
--
Greg Maxey/Word MVP
A Peer in Peer to Peer Support

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 Total100, then round (Total-100)/50*Rate, ELSE 0.

Any thoughts would be greatly appreciated. Thanks! 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

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
 Thread Tools Show Printable Version Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Microsoft Word Newsgroups     Microsoft Word Help     New Users     Tables     Formatting Long Documents     Mailmerge     Page Layout About WordBanter     About this forum Similar Threads Thread Thread Starter Forum Replies Last Post stacyo Microsoft Word Help 4 December 8th 04 04:52 PM StarWine Microsoft Word Help 3 December 6th 04 06:17 PM Peter Thys Microsoft Word Help 1 December 6th 04 01:14 PM Mitch Microsoft Word Help 2 November 29th 04 07:29 PM Christian Microsoft Word Help 1 November 28th 04 06:14 PM

All times are GMT +1. The time now is 07:34 PM. Copyright ©2004-2023 Microsoft Office Word Forum - WordBanter.