Rounding to nearest 5 cents?

[johnnyrad]

Hi, i have a sheet and i want to take column A and round the figures i
it to the nearest 5 cents. Is there a way to do that with the roun
function? I couldnt see how you would specifiy that you only want 5 o
0 as the cent's digit.... any help is appreciated!
Chri

 

[Norman Harker]

Hi Chris!

Use:
=ROUND(A1/0.05,0)*0.05

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

 

[nathandaviesuk]

Try this: =ceiling(A1,0.5)

A1 being the chosen cell , 0.5 being the number to round up to

 

[Ron Rosenfeld]

Try this: =ceiling(A1,0.5)

A1 being the chosen cell , 0.5 being the number to round up to.

That always rounds up the next highest 50 cents. The OP wanted to round to the
closest 5 cents.


--ron

 

[theillknight]

This may be an ugly solution, but I think it works. Assume A1 is th
referenced cell you want rounded to the nearest five cents.

=IF(MOD(A4,0.1)>=0.075,ROUNDUP(A4,1),IF(AND(MOD(A4,0.1)>=0.025,MOD(A4,0.1)<0.075),ROUNDDOWN(A4,1)+0.05,ROUNDDOWN(A4,1)))

Mod takes the first number and divides it by the second, then output
the remainder. So, if you have mod(1.28,.1), the output is .08. Th
IF statement then checks that number and produces a result. If th
remainder > .075, it'll round up to the .10 cent. If it's between .02
and .075, it'll round to the .05 cent. If it's less than .025, it'l
round to the .00 cent.

Try it out and see if it works

 

[tmagdy]

I believe Ron Rosenfield was on the right track when he suggested usin
the Ceiling function. It was just that the second parameter he gav
should have been .05, not .5. See another post of mine on this

http://www.excelforum.com/showthread.php?s=&postid=590882#post59088

 

[theillknight]

tmagdy,

The problem with ceiling/floor functions are they round up and dow
respectively. For instance, ceiling(1.26,.05) outputs 1.30, but
believe our friend wants it to round to 1.25.

My method listed above is cumbersome, but accurate (I think)

 

[tmagdy]

Sorry, misunderderstood, then perhaps try the MROUND function (you nee
to add in the Analysis toolpack Add-In)

=MROUND(1.26, .05) gives 1.25
=MROUND(1.19, .05) gives 1.20
=MROUND(1.32, .05) gives 1.30

Seems to provide the same result as your function

 

[Ron Rosenfeld]

This may be an ugly solution, but I think it works. Assume A1 is the
referenced cell you want rounded to the nearest five cents.

=IF(MOD(A4,0.1)>=0.075,ROUNDUP(A4,1),IF(AND(MOD(A4,0.1)>=0.025,MOD(A4,0.1)<0.075),ROUNDDOWN(A4,1)+0.05,ROUNDDOWN(A4,1)))

Mod takes the first number and divides it by the second, then outputs
the remainder. So, if you have mod(1.28,.1), the output is .08. The
IF statement then checks that number and produces a result. If the
remainder > .075, it'll round up to the .10 cent. If it's between .025
and .075, it'll round to the .05 cent. If it's less than .025, it'll
round to the .00 cent.

Try it out and see if it works.

=ROUND(A1/0.05,0)*0.05

as previously posted by Norman, is much simpler.


--ron

 

[rodtviv]

try this
=TRUNC(A1+0.5,0)

 

[Ron Rosenfeld]

try this
=TRUNC(A1+0.5,0)

It's short. =A1 is even shorter.


--ron

 

[thessels]

Hey guys, don't make it more complicated than necessary. Genius is i
simplicity!

=ROUND(2*A1;1)/2

Double the number, round to one digit and devide by two gives a perfec
result.

Thijs

 

[Jim_Jammy]

Thessels answer is nice and simple and works well.

Here is a solution using the Round and Trunc function which many of yo
were attempting........

=IF(SUM(A1-(TRUNC(A1,1))>=0.075),SUM(ROUND(A1,1)),IF(SUM(A1-(TRUNC(A1,1))>=0.025),SUM(TRUNC(A1,1)+0.05),(TRUNC(A1,1))

 

[Ron Rosenfeld]

Hey guys, don't make it more complicated than necessary. Genius is in
simplicity!

=ROUND(2*A1;1)/2

Double the number, round to one digit and devide by two gives a perfect
result.

Thijs.

And that is virtually the same as the original answer posted by Norman.

=ROUND(A1/0.05,0)*0.05

It has the advantage over several of the posts of being responsive to the
question asked by the OP.

Some of these non-responsive answers rounded UP instead of to the nearest; some
rounded to the nearest .5 instead .05; etc.




--ron

 

[johnnyrad]

Thanks guys, all useful info