Rounding to the nearest 5 cents

  • Thread starter Thread starter Tim H
  • Start date Start date
T

Tim H

Hello,

My problem is that I have a column of 3,000 currency
values. However I need them all to be rounded to the
nearest 5 cents.
I am not a very experienced excel user!
Can anybody help me?

Thankyou,

Tim H
 
Hi Tim,

Not sure if this is the best way but it will work.

(Formaulas in Column B below)
=IF(INT(RIGHT(A2,1))<5,FLOOR(A2,0.05),CEILING(A2,0.05))

Example:

A B
1 Value New Value
2 125.62 125.60
3 97.26 97.30

Hope that helps.

Regards,
James S
 
More generally, to round to the nearest X:

multiply 1/X, then
round to the nearest whole number, and then
multiply by X

Alan.
 
Hello,

My problem is that I have a column of 3,000 currency
values. However I need them all to be rounded to the
nearest 5 cents.
I am not a very experienced excel user!
Can anybody help me?

Thankyou,

Tim H
Click to expand...

=ROUND(A1/0.05,0)*0.05


--ron
 
Back
Top