Round a number

  • Thread starter Thread starter rodolform
  • Start date Start date
R

rodolform

It may sound silly but I can´t find a solution to this:

I need to round up a number when the decimal part is greater than .
but it needs to be round DOWN when the decimal part is equal or les
than .5

Example:
1.5 = 1
1.6= 2

12.4= 12
12.8= 13

Can anyone help me??

Thank's in advance
Rodolf
 
Thanks Rich.

Now I have another problem, my excel is in spanish and I can't find th
function MOD, can you tell me what it does or better do you know th
name of that function for excell in spanish??

Rodolf
 
Hi Rodolpho!

In Spanish Mod is Residuo

I have a sheet with all Spanish translations if you want to e-mail me
for it.

Or there's a translator available from:

Eric Desart
http://www.acoustics-noise.com/

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman:
Thank you for the help.
Now that I know the function in spanish iI have tried but it does no
what I want, it does work when the decimal part is .5 but if I chang
this value to a diferent one it does not work.

Maybe further explication will help.

I have a series of values that need to be rounded if they are within
certain value (this value varies) so I may have:

A1 Needs to be rounded if decimal part is less than .3 and rounded u
if decimal part is equal or greater than .3 so it will return this:
2.2 = 2
3.4= 4
In A2 I need to do the same but with a decimal value of .5 so it wil
return
2.2 =2
3.4= 3
3.5=4
6.7 = 7


Every cell will have a specific value to compare and round the number.

Thak you again
 
Hi Rodolfo!

A1 is an unusual request met by:

=IF(MOD(A1,1)<0.3,ROUND(A1,0),ROUNDUP(A1,0))

A2 is "normal"
=ROUND(A2,0)

You should now have the translations sheet:

ROUND = REDONDEAR
ROUNDUP = REDONDEAR.MAS

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman:

That did it, if I'm not wrong now I can change the value .3 in th
formula IF(MOD(A1,1)<0.3,ROUND(A1,0),ROUNDUP(A1,0)) if I want to set
diferent value when the number has to be rounded, but have to chang
round for roud down to prevent rounding up after .5

So IF(MOD(A1,1)<.7, round.down(a1,0),roundup(a1,0)) will do it.

Any comments???

Thank you .....

Rodolfo
 
Norman:
Thank you for the help.
Now that I know the function in spanish iI have tried but it does not
what I want, it does work when the decimal part is .5 but if I change
this value to a diferent one it does not work.

Maybe further explication will help.

I have a series of values that need to be rounded if they are within a
certain value (this value varies) so I may have:

A1 Needs to be rounded if decimal part is less than .3 and rounded up
if decimal part is equal or greater than .3 so it will return this:
2.2 = 2
3.4= 4
In A2 I need to do the same but with a decimal value of .5 so it will
return
2.2 =2
3.4= 3
3.5=4
6.7 = 7


Every cell will have a specific value to compare and round the number.
Click to expand...

A general formula:

=IF(MOD(Number,1)<RoundValue,INT(Number),ROUNDUP(Number,0))

Number is the number you wish to round.
RoundValue is the decimal part that you wish to compare to determine whether to
round up or round down.

You could have your Numbers in Column A, and the Associated RoundValues in
Column B. The formula could then go into Column C.


--ron
 
Hi Rodolfo!

You could put your boundary in a cell in the row that it applies to.

For example we could use column G.

We then get a general formula:

=IF(MOD(A1,1)<G1,ROUNDDOWN(A1,0),ROUNDUP(A1,0))

This is essentially the same as what Ron has suggested.
ROUNDDOWN(A1,0) is the same as using INT(A1) but you may find it more
explicit.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top