rounding cents in spreadsheet (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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
=ROUNDUP(A1*2+0.01,0)/2-0.03

You can adjust the point where it rounds to .47 or .97 by changing the “A1*2+0.01” 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

=MROUND(A1+0.03,0.5)-0.03

Reply | Quote

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

Reply | Quote

Hi BFV

I am pleased it worked for you.

Reply | Quote

Hi BFV

I am pleased it worked for you.

Reply | Quote

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

Reply | Quote

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
=ROUNDUP(A1*2+0.01,0)/2-0.03

You can adjust the point where it rounds to .47 or .97 by changing the “A1*2+0.01” 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

=MROUND(A1+0.03,0.5)-0.03

Reply | Quote