Set price to end in .%9 or keep at .%5

Copper Contributor

I am working in file where I want my prices to either end in .%9 or .%5. I put the "%" because they can be any number. For example, I have a price of 68.55 and want to keep that the same since it ends in 5. I have another price of 61.47 and want that price to end in .49( it would be 61.49). I have used the round function but don't know how to let Excel know to keep ones ending in 5 as the same.

 

I've changed all numbers to end in 9 but not keep it to end in 5. Let me know

1 Reply

@NOVICEEXCEL 

If you always want to round up, so 68.50 becomes 68.55, use a formula such as

=(ROUNDDOWN(100*A2,-1)+5+4*(MOD(100*A2,10)>5))/100

where A2 is a cell with the 'raw' price.

If you want 68.54 to be rounded down to 68.49, and 68.56 to be rounded up to 68.59, use

=IF(MOD(100*A2,10)=5,E5,ROUND(A2,1)-0.01)