#1   Report Post  
Dawn Rhoads
 
Posts: n/a
Default 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!
  #2   Report Post  
Charles Kenyon
 
Posts: n/a
Default

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

"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!



  #3   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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!



  #4   Report Post  
Dawn Rhoads
 
Posts: n/a
Default

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

"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!




  #5   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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
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.

"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!





  #6   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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
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.

"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!



  #7   Report Post  
Dawn Rhoads
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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!




  #8   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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!



  #9   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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!



  #10   Report Post  
Dawn Rhoads
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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!






  #11   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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!



  #12   Report Post  
Greg Maxey
 
Posts: n/a
Default

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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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" 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

Dawn Rhoads wrote:
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!



  #13   Report Post  
Charles Kenyon
 
Posts: n/a
Default

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.

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

Thanks again for any help you can offer!

"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

Dawn Rhoads wrote:
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!






  #14   Report Post  
Dawn Rhoads
 
Posts: n/a
Default

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
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
form and form field formatting stacyo Microsoft Word Help 4 December 8th 04 04:52 PM
Text Form Field Ref in Footer Won't Update on Screen StarWine Microsoft Word Help 3 December 6th 04 06:17 PM
Prevent double quote in form field Peter Thys Microsoft Word Help 1 December 6th 04 01:14 PM
Form Field Automation Mitch Microsoft Word Help 2 November 29th 04 07:29 PM
Field code # characters (no spaces) FOR SPECIFIC DOC SECTION Christian Microsoft Word Help 1 November 28th 04 06:14 PM


All times are GMT +1. The time now is 07:34 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"