Rounding Formula Question

[ChrisHockley]

Hello all,

Thanks in advance for any help you can offer.

Rounding - I'm working on some pricing and once the pricing is done I am adding a percentage to convert these prices to MSRP. This of course creates all kinds of random dollar amounts with no consistency. I need help setting up a formula that would do the following to the price (x)

a. if x is less than or equal to .11 over the nearest dollar then round down to .99 cents of the previous dollar
b. if x is .12 to .24 over the dollar round to that dollar and .19 cents
c. if x is .25 to .40 over the dollar round to that dollar and .29 cents
d. if x is .41 to .65 over the dollar round to that dollar and .49 cents
e. if x is .66 to .90 over the dollar round to that dollar and .79 cents
f. if x is .91 to .10 over the dollar round to the closest dollar and .99 cents

Wow... I just got a stress headache typing that out let alone the formula!

All I would greatly appreciate any and all attempts to assist!

Chris H.

 

[Eric W]

I just knew there was a trick to creating prices!

Try this:

	B	C

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"] $ 1.36 [/TD]
[TD="align: right"] $ 1.29 [/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] $ 2.94 [/TD]
[TD="align: right"] $ 2.99 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"] $ 9.24 [/TD]
[TD="align: right"] $ 9.19 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] $ 7.99 [/TD]
[TD="align: right"] $ 7.99 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] $ 1.19 [/TD]
[TD="align: right"] $ 1.19 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"] $ 8.46 [/TD]
[TD="align: right"] $ 8.49 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"] $ 9.92 [/TD]
[TD="align: right"] $ 9.99 [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"] $ 7.28 [/TD]
[TD="align: right"] $ 7.29 [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"] $ 4.53 [/TD]
[TD="align: right"] $ 4.49 [/TD]

[TD="align: center"]10[/TD]
[TD="align: right"] $ 5.08 [/TD]
[TD="align: right"] $ 4.99 [/TD]

[TD="align: center"]11[/TD]
[TD="align: right"] $ 3.09 [/TD]
[TD="align: right"] $ 2.99 [/TD]

[TD="align: center"]12[/TD]
[TD="align: right"] $ 1.06 [/TD]
[TD="align: right"] $ 0.99 [/TD]

[TD="align: center"]13[/TD]
[TD="align: right"] $ 6.74 [/TD]
[TD="align: right"] $ 6.79 [/TD]

[TD="align: center"]14[/TD]
[TD="align: right"] $ 8.42 [/TD]
[TD="align: right"] $ 8.49 [/TD]

[TD="align: center"]15[/TD]
[TD="align: right"] $ 9.67 [/TD]
[TD="align: right"] $ 9.79 [/TD]

[TD="align: center"]16[/TD]
[TD="align: right"] $ 8.41 [/TD]
[TD="align: right"] $ 8.49 [/TD]

[TD="align: center"]17[/TD]
[TD="align: right"] $ 8.12 [/TD]
[TD="align: right"] $ 7.99 [/TD]

[TD="align: center"]18[/TD]
[TD="align: right"] $ 6.29 [/TD]
[TD="align: right"] $ 6.29 [/TD]

[TD="align: center"]19[/TD]
[TD="align: right"] $ 9.51 [/TD]
[TD="align: right"] $ 9.49 [/TD]

[TD="align: center"]20[/TD]
[TD="align: right"] $ 1.03 [/TD]
[TD="align: right"] $ 0.99 [/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=INT(B1)+LOOKUP(MOD(B1,1),{0,0.12,0.25,0.41,0.66,0.91},{-0.01,0.19,0.29,0.49,0.79,0.99})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisHockley]

holy crap... that works... I'm admittedly scratching my head and wondering how to interpret this masterpiece... lots of elements I haven't seen before. INT, LOOKUP, MOD.

Grateful. Awed and appreciative.

Thanks!

 

[Eric W]

INT (integer) gives you the dollar amount without the cents.

MOD(B1,1) gives you the cents amount without the dollars.

LOOKUP finds the location in the first array where the cents goes (equal or greater than one value, less than the next), and gets the corresponding value from the second array. So it basically finds the cents value to use for the range you gave, and adds that to the dollar amount.

Happy I could help!

 

[ChrisHockley]

Eric,

Thank you for the explanation!!!

A question for you. This rounding formula you have here is perfect for items that I say 5 dollars and less. Is there a way to build into the formula the following parameters or does each need to have it's own formula as i have been doing? Up to this point i have been using these by sorting the dollar values then pasting the following.

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 2"]$5 to $20[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROUNDUP(x/0.5,0)*0.5-0.01[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Under $50[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=IF(A1-INT(A1)<0.1,INT(A1),ROUNDUP(A1,0))-0.01[/TD]
[/TR]
[TR]
[TD="colspan: 2"]anything under .10 over the 1 will round down[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Over $50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]=MROUND(A1+1.49,5)-0.01[/TD]
[/TR]
[TR]
[TD="colspan: 2"]anything under 1 over the 5 will round down[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Over $500[/TD]
[/TR]
[TR]
[TD="colspan: 2"]=MROUND(A1+1.49,10)-0.01[/TD]
[/TR]
[TR]
[TD="colspan: 2"]anything under 3.50 over the 10 will round down[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Well, if you want to combine all those formulas into one, you could use LOOKUP to decide the range for each formula, then use CHOOSE to use the formula.

=CHOOSE(LOOKUP(A1,{0,5,20,50,500},{1,2,3,4,5}),INT(A1)+LOOKUP(MOD(A1,1),{0,0.12,0.25,0.41,0.66,0.91},{-0.01,0.19,0.29,0.49,0.79,0.99}),ROUNDUP(2*A1,0)/2-0.01,IF(MOD(A1,1)<0.1,INT(A1),ROUNDUP(A1,0))-0.01,MROUND(A1+1.49,5)-0.01,MROUND(A1+1.49,10)-0.01)

 

[ChrisHockley]

I'm literally speechless and forever grateful.

 

[ChrisHockley]

Eric,

Would you be able to assist me in further refining this formula you have created? It does exactly what I had needed it to do however in using it I have found errors of my own making as I left out details that I did not think of at the time...

$5 to $20
ROUNDUP(x/0.5,0)*0.5-0.01
It would be ideal if anything from .75 of the previous the dollar to .24 cents above the dollar would round up/down to .99 of the dollar. Anything .25 to .74 above the dollar would round up/down to the .49.

 

[Eric W]

Try:

=CHOOSE(LOOKUP(A1,{0,5,20,50,500},{1,2,3,4,5}),INT(A1)+LOOKUP(MOD(A1,1),{0,0.12,0.25,0.41,0.66,0.91},{-0.01,0.19,0.29,0.49,0.79,0.99}),MROUND(A1,0.5)-0.01,IF(MOD(A1,1)<0.1,INT(A1),ROUNDUP(A1,0))-0.01,MROUND(A1+1.49,5)-0.01,MROUND(A1+1.49,10)-0.01)

 

[ChrisHockley]

worked like a charm! Thanks yet again.