Excel round to the nearest
 

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



--
lmo

Excel round to the nearest
 

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 D2:D11
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


"Lisa" wrote:

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



--
lmo

Excel round to the nearest
 

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


"John C" wrote:

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 D2:D11
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


"Lisa" wrote:

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



--
lmo