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

%3CLINGO-SUB%20id%3D%22lingo-sub-3074295%22%20slang%3D%22en-US%22%3ESet%20price%20to%20end%20in%20.%259%20or%20keep%20at%20.%255%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074295%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20in%20file%20where%20I%20want%20my%20prices%20to%20either%20end%20in%20.%259%20or%20.%255.%20I%20put%20the%20%22%25%22%20because%20they%20can%20be%20any%20number.%20For%20example%2C%20I%20have%20a%20price%20of%2068.55%20and%20want%20to%20keep%20that%20the%20same%20since%20it%20ends%20in%205.%20I%20have%20another%20price%20of%2061.47%20and%20want%20that%20price%20to%20end%20in%20.49(%20it%20would%20be%2061.49).%20I%20have%20used%20the%20round%20function%20but%20don't%20know%20how%20to%20let%20Excel%20know%20to%20keep%20ones%20ending%20in%205%20as%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20changed%20all%20numbers%20to%20end%20in%209%20but%20not%20keep%20it%20to%20end%20in%205.%20Let%20me%20know%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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)