Excel round to the nearest

[Lisa]

I need to create a formual to round to the nearest based on the rules below.
Is there a macro that can do this?

If price is below $50.00 Round up to a .99 ending
If price is $50.01 to $100.00 Round up or down to the nearest 4.99 or 9.99
ending
If price is $100.01 to $500.00 Round up to a 9.99 ending
If price is $500.01 to $1000.00 Round up to nearest 19.99 – 39.99 – 49.99 –
69.99 – 89.99 or 99.99 ending
If price is $1000.01 to $2000.00 Round up to nearest 29.99 – 49.99 – 69.99
or 99.99 ending
If price is $2000.01 and up Round up to 49.99 or 99.99 ending

 

[John C]

Took a little time, but needed it. This is non-VBA, and requires to create 2
tables. I put these tables on a separate tab. I will tell what entries I used
before I give you the formula by giving you column/row information, and then
showing what I entered in each cell. On Table tab
Cells A2:A7
0 | 50.01 | 100.01 | 500.01 | 1000.01 | 2000.01
Cells B2:B7
1 | 2 | 3 | 4 | 5 | 6
Highlight A2:B7, Insert-->Name-->Define, named range is Tbl_Category

Cells D211
0 | 9.99 | 14.99 | 29.99 | 39.99 | 44.99 | 59.99 | 79.99 | 84.99 | 94.99
Cells E2:E11
-0.01 | 19.99 | 19.99 | 39.99 | 39.99 | 49.99 | 69.99 | 89.99 | 89.99 | 99.99
Cells F2:F11
-0.01 | -0.01 | 29.99 | 29.99 | 49.99 | 49.99 | 69.99 | 69.99 | 99.99 | 99.99
Highlight D2:F11, Insert-->Name-->Define, named range is Tbl_Category45

Then your formula would be
=IF(ISNUMBER(A1),CHOOSE(VLOOKUP(A1,Tbl_Category,2,TRUE),MAX(INT(A1-0.49)+0.99),MAX(INT((A1-2.49)/5)*5+4.99),MAX(INT((A1-4.99)/10)*10+9.99),INT(A1/100)*100+VLOOKUP(MOD(A1,100),Tbl_Category45,2,TRUE),INT(A1/100)*100+VLOOKUP(MOD(A1,100),Tbl_Category45,3,TRUE),MAX(INT((A1-24.99)/50)*50+49.99)),"")

Hope this helps!

 

[John C]

All that effort, and not even a peep from the OP, *sigh*.

 

[Lisa]

I did have to modify the formula because every price should round up except
for rule of $50.01 to $100.00 which can round up or down. I just changed the
range name fields to match the appropriate roundup price.

thanks