Rounding Up For Retail Pricing

[Guest]

Could someone please help me with the formula to always round up To 5's and
9's in the hundredths digit. For example if the amount is $3.20- $3.25 it
would round to $3.25. If the the number is $3.26 - $3.29, it would round up
to $3.29. I am using the following formula already to calculate pricing from
cost in Excel 2003 and currency formulations. =(I3/9)/.54+1.12

 

[Ian]

Assuming your price is in A1

=IF(INT(A1*10)>INT((A1-0.06)*10),(INT(A1*10)+0.5)/10,(INT(A1*10)+0.9)/10)

 

[Guest]

Assuming your data is in E7

=ROUNDDOWN(E7,1)+VLOOKUP(ROUND(E7-ROUNDDOWN(E7,1),2),{0,0.05;0.06,0.09},2,TRUE)

 

[Guest]

I believe analysis toolpak would need to be installed for the Rounddown
function to work.

Tools/Add Ins - check Analysis toolpak.

 

[JE McGimpsey]

One way:

=CEILING(I3/4.86+1.12,0.05)-(MOD(I3/4.86+1.12,0.1)>0.05)/100

 

[Guest]

So if the penny digit is between 0 and 5 -> make it 5 and if it is 6 to 9 ->
make it 9.

Call your formula C10 for reading pleasure:

10*(10*C10-INT(10*C10) returns the penny as an integer

=IF(10*(10*C10-INT(10*C10))>5,
(10*INT(10*C10)+9)/100,
(10*INT(10*C10)+4)/100)

 

[JE McGimpsey]

I believe analysis toolpak would need to be installed for the Rounddown
function to work.

Nope. It's built-in. Perhaps you were thinking of MROUND().

 

[Guest]

I must have been confused. Thanks for pointing that out. Also - interesting
reading on excel passwords (different post).

 

[Guest]

Thank you JE, JMB & Ian - I'll try when I get back to work.~ appreciate
everyone's knowledge and willingness to help~ bound4LIFER