Round up

[Cabo]

I've got a table and by the latest rule, the result must be round up to 5 euro

Example: the result is 625,51 and the endresult must be 630,0
the result is 328,44 and the endresut must be 330,0

How do I round up in a table in Word ?

 

[Greg Maxey]

Cabo,

I am not familiar with the use of a comma for separating
numbers. If the format used a decimal point, the
following formula construction would work. This assumes
you are using {=sum(above)} as your basic calculation:

{ =Round({=sum(above0},-1)}

 

[Cabo]

Greg

I'm sorry, but it doesn't work
{ =Round({=sum(above0},-1)} don't show it
It changed not in result
Is the formula correct ?

 

[Greg Maxey]

Cabo,

It looks like I sent you a typo.

The formula should be {=Round({=Sum(above)},-1)}

Note the { } must be entered with CTRL+F9.
The basic formula is {=Round(x,y)} where x is the
expression that returns the initial pre-rounded value and
y determines how many decimal places.

 

[Greg Maxey]

Cabo,

Disregard my last. On relection, I see that I have done a
very poor job answering your question. The method I
provided will round, but not necessarily up and definately
not in 5,00 Euro increments.

I am sorry for wasting your time. If I can find a correct
solution I will post again.

 

[Greg Maxey]

Cabo,

I think I have it sorted out now.

{ IF { =round){ =sum(above) }, -1)} < { =sum(above)}"{ =({
=round){ =sum(above) }, -1)}+5.00) }"{ =round){ =sum
(above) }, -1)} \# #.00 }

Field code brackets { } are inserted with CTRL+F9. If
your system is set to recognize a "comma" as the decimal
separator, then replace the "periods" above with "commas."

Basically the round field is performing the funtion of
rounding the sum to the nearest 10.00 Euros. "IF" is
rounds down, then the nested formula field is adding 5.00
Euros to the result. If it rounds up then the round up
result is displayed. The \# #.00 swithc forces the diplay
of the decimal zeros.

Again I am sorry for posting such a half arsed reply
earlier.

HTH, Greg

 

[Greg Maxey]

Cabo,

If you are still with me, I have found a more efficient
method. We owe our knowledge of the following to a being
that refers to itself as "macropod."

Use

{ =round({sum(above)}/5+.5,0)*5 \# #.00}

Thanks macropod!!

 

[Cabo]

I'm sorry Greg, bur i've got a "syntaxis error".
I have copied your formula and changed the lay-out of numbers.
It doesn't works.

 

[Greg Maxey]

Cabo,

I you will post back with the exact formula you are using I will see if I
can make it work. I usually find that Syntax errors means that I am very
close to getting it right

 

[Greg Maxey]

Cabo,

No it is me again that should be sorry. I looked at the
last forumla I sent you and realized I had omitted a key
component (the = sign in the sum(above) field).

Since you can't copy and past the actually field code I
messed it up retyping it to send it on to you. I have
confirmed that this works (3 times):

{ =round({=sum(above)}/5+.5,0)*5 \# #.00}

Once again, if you use commas as your decimal separators
then modify as necessary.

 

[macropod]

Hi Greg,

I'd suggest:
{=round((sum(above)/5+(mod(sum(above),5)<>0)),0)*5 \# 0.00}
since you don't want to add 5 to zeros or values that are themselves
divisible by 5!

Cheers
PS: Note also that you don't need to embed sum(above) with its own set of
field braces.

 

[Greg Maxey]

Thanks again, macropod.

 

[Greg Maxey]

Macropod,

Could you explain the behavior of the mod portion of your
formula? In particular the <>0 portion of (=mod(sum
(above),5)<>0)

{=mod(125.01,5)} returns 0.01

{=mod(125.01,5)<>0) returns 1 which is obviously key to
the rest of the formula rounding up to 130.00.

If I look at this by the component parts, I can construct
{=0.01<>0} which also returns 1. Just out of curiosity I
enter {=500<>0} this also also returns 1!!

Why is that? Hold on a minute. This is the work of TRUE
or FALSE functions isn't it!

I went to the trouble of typing this so I am sending
anyway to serve as a note of thanks. You got me to engage
the brain this morning.

Cheers.

 

[macropod]

Hi Greg,

Glad to see you've figured it out - much more rewarding than having it
explained!!

Just for the benefit of anyone else: The mod function (as in
mod(sum(above),5)) divides the number in the 1st part of the expression
(sum(above)) by the number in the second part (5) and returns just the
remainder. I then wrapped a TRUE/FALSE test around it, thus:
(mod(sum(above),5)<>0). In a formula such as this, the result of the
TRUE/FALSE test to expresses itself as 1 (TRUE) or 0 (FALSE). To use this
for rounding up, I simply added the TRUE/FALSE test's result to the value
being rounded - in this case sum(above)/5.

Cheers