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!

=A1+CHOOSE(RIGHT(A1*100,1)+1,-0.01,0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)

--
Gary''s Student - gsnu200845

Try

=IF(OR(MOD(A1,INT(A1))0.25,MOD(A1,INT(A1))=0),ROU ND(A1,1)-0.01,CEILING(A1,0.25))

Mike

"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!

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!

This isn't elegant, but seems to work:

=IF(MOD(CEILING(E4,0.05)*100,10)=0,CEILING(E4,0.05 )-0.01,CEILING(E4,0.05))

Hope this helps,

Hutch

"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!

That's what I came up with also - and it took me the time it did you 4 others
to get it right!

"Gary''s Student" wrote:

=A1+CHOOSE(RIGHT(A1*100,1)+1,-0.01,0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)

--
Gary''s Student - gsnu200845

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!

Mike your formula still provided a couple incorrect results...

$4.19 became 4.25 (should have stayed as is)
$2.74 became $2.69, should have been $2.75
$2.44 became $2.39

"Mike H" wrote:

Try

=IF(OR(MOD(A1,INT(A1))0.25,MOD(A1,INT(A1))=0),ROU ND(A1,1)-0.01,CEILING(A1,0.25))

Mike

"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!

Tom your formula was close, but there were still a few rounding issues:

$3.40 remained $3.40
$2.50 became $2.55
$2.38 became $2.40

I'm guessing that I will need to truncate the original value at only two
decimal points and then see if the formula works. What would I need to do to
truncate the original value two only two decimal points? (e.g. 4.487563
becomes 4.49)

Thanks
"Tom Hutchins" wrote:

This isn't elegant, but seems to work:

=IF(MOD(CEILING(E4,0.05)*100,10)=0,CEILING(E4,0.05 )-0.01,CEILING(E4,0.05))

Hope this helps,

Hutch

"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!

Try this:

=CEILING(A1,0.05)-(OR(--RIGHT(A1*100)={0,6,7,8,9}))*0.01


"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!

From your description & examples, I thought we were only dealing with dollars
& cents. From my further testing, it looks like only Sheeloo's second formula
works correctly. Here is a revised version of mine that also works:

=IF(MOD(CEILING(ROUND(A1,2),0.05)*100,10)<0.005,CE ILING(ROUND(A1,2),0.05)-0.01,CEILING(ROUND(A1,2),0.05))

Besides the need to round of the extra decimals, I saw that MOD was not
returning zero, but a very small value like 0.000000000000000567. Those two
factors caused the erroneous results in my previous formula.

Hutch

"GoBucks" wrote:

Tom your formula was close, but there were still a few rounding issues:

$3.40 remained $3.40
$2.50 became $2.55
$2.38 became $2.40

I'm guessing that I will need to truncate the original value at only two
decimal points and then see if the formula works. What would I need to do to
truncate the original value two only two decimal points? (e.g. 4.487563
becomes 4.49)

Thanks
"Tom Hutchins" wrote:

This isn't elegant, but seems to work:

=IF(MOD(CEILING(E4,0.05)*100,10)=0,CEILING(E4,0.05 )-0.01,CEILING(E4,0.05))

Hope this helps,

Hutch

"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!