# Thread: rounding cents in spreadsheet (Excel 2000)

1. ## rounding cents in spreadsheet (Excel 2000)

I have 1500 prices and i need a formula to round the cents to .47 or .97
Example:
8.84 would round to-8.97
7.99 ...... 7.97
11.99 ..... 11.97
6.20 ..... 6.47
2.99.... 2.97
5.35 .... 5.47
There will be a whole spread sheet of these. I work in a store and if i don't figure out a formula that will do this, I will be hand punching each one!
I would dearly appreciate help.

2. ## Re: rounding cents in spreadsheet (Excel 2000)

Hi & welcome to the Lounge

The exact method you are using to round is not clear, as 6.20 rounds to 6.47 you are not rounding to the nearest .47 or .97

With the following formula cents from .00 to .49 are rounded to .47 and from .50 to .99 rounded to .97
<code>=ROUNDUP(A1*2+0.01,0)/2-0.03</code>

You can adjust the point where it rounds to .47 or .97 by changing the "<code>A1*2+0.01</code>" part of the formula. Instead of +0.01 add or subtract the amount necessary to achieve your desired result. +0.01 is probably the maximum you would want to add and you may need to subtract amounts to achieve the desired result.

If you did intend to round to the nearest .47 or .97 then you can use the following formula instead, for it to work the Analysis ToolPak add-in must be installed

<code>=MROUND(A1+0.03,0.5)-0.03</code>

3. ## Re: rounding cents in spreadsheet (Excel 2000)

tony55
Thank you sooooooo much. I just changed 2045 prices using the first formula you gave me. I don't really understand how or why it works, but it did and I thank you again!

BFV

4. ## Re: rounding cents in spreadsheet (Excel 2000)

Hi BFV

I am pleased it worked for you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•