Rounding to nearest .05 or .09 cents.

**Kakita** · 02-03-2013, 10:01 PM

I have a large group of numbers to round for a Sales Catalog where prices need to end in XX.X5 or XX.X9

How do you Round a numbers in Microsoft Excel to the nearest .05 cent or .09 Cents, Whichever is closer.

For example: 1.06 would round to 1.05 and 1.08 would round to 1.09.

I have over 1000 numbers to round in this way. Is there a way to do this?

**vlady** · 02-03-2013, 10:26 PM

how about
1.01 -> is this 1.00 or 1.05 also
1.02
1.03
1.04

**FDibbins** · 02-03-2013, 10:36 PM

Also...

1.06 would round to 1.05 and 1.08 would round to 1.09.

normally is 1.06 = 1.05, then 1.9 is usually 1.10?

**kvsrinivasamurthy** · 02-04-2013, 01:19 AM

Try this. It converts 0.02---0.06 to xx.x5, rest it will convert to xx.x9

Please Login or Register  to view this content.

**vlady** · 02-04-2013, 01:29 AM

you can also try it like this.

Formula:

Please Login or Register  to view this content.

or this one

Formula:

Please Login or Register  to view this content.

**Kakita** · 02-04-2013, 07:30 PM

So far the "=MROUND(A1, 0.05)-IF(RIGHT(MROUND(A1, 0.05))="5",0,0.01)" formula has worked out nearly perfectly.

The only problem is that it will spit out numbers of .50 that i want to be .49. I need it to only spit out numbers ending in .X9 or .X5.

Here is an example of numbers I've converted below. ( I Have like 2000 numbers to change )

Numbers.jpg

Thanks for all the help guys, its making my life so much easier. =)