With your numbers in A1, A2,... try this in B1 and copy down
=LOOKUP(RIGHT(TEXT(A1,"0.00"),1)*1,{0,1,2,3,4,5,6, 7,8,9},{9,5,5,5,5,5,9,9,9,9})
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"GoBucks" wrote:

I'm working on a pricing worksheet that calculates a product cost +
freight+margin to come up with a suggested product price. What I'd like to
be able to do is write a formula that converts all pricing to end with a 5 or
9. If the price already ends in a 5 or 9, nothing should change. If the
price ends in a 1,2,3 or 4, it should roundup to a 5. If the price ends in
6,7,8 it should round up to 9. If the pricing ends in zero, it should round
down to 9.

For instance:
$4.24 becomes $4.25
$3.26 becomes $3.29
$2.00 becomes $1.99

I'm guessing this will result in a doozy of a formula, but thought I'd
check.

Thanks in advance for your replies!

Sorry, for the incomplete answer...

One way is to use this formula
=LOOKUP(RIGHT(TEXT(A1,"0.00"),1)*1,{0,1,2,3,4,5,6, 7,8,9},{-1,4,3,2,1,0,3,2,1,0})/100+LEFT(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00")-1)*1)
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Sheeloo" wrote:

With your numbers in A1, A2,... try this in B1 and copy down
=LOOKUP(RIGHT(TEXT(A1,"0.00"),1)*1,{0,1,2,3,4,5,6, 7,8,9},{9,5,5,5,5,5,9,9,9,9})
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"GoBucks" wrote:

I'm working on a pricing worksheet that calculates a product cost +
freight+margin to come up with a suggested product price. What I'd like to
be able to do is write a formula that converts all pricing to end with a 5 or
9. If the price already ends in a 5 or 9, nothing should change. If the
price ends in a 1,2,3 or 4, it should roundup to a 5. If the price ends in
6,7,8 it should round up to 9. If the pricing ends in zero, it should round
down to 9.

For instance:
$4.24 becomes $4.25
$3.26 becomes $3.29
$2.00 becomes $1.99

I'm guessing this will result in a doozy of a formula, but thought I'd
check.

Thanks in advance for your replies!