Jan 27 2022 10:50 AM
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
Jan 27 2022 11:22 AM
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)