Rounding to nearest 5 cents?

  • Thread starter Thread starter johnnyrad
  • Start date Start date
J

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
 
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.
 
Try this: =ceiling(A1,0.5)

A1 being the chosen cell , 0.5 being the number to round up to
 
Try this: =ceiling(A1,0.5)

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

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


--ron
 
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,

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)
 
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
 
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.
Click to expand...

=ROUND(A1/0.05,0)*0.05

as previously posted by Norman, is much simpler.


--ron
 
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
 
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))
 
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.
Click to expand...

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
 
Back
Top