Rounding Prices ends with 4.99

[ankrups]

Hi,

I have unique requirement. I am wholesaler of furniture and other household products. I need to sell my products which ends with 4.99. I usually get the data feed from different suppliers but I need to write a formula in excel that when pricing data loaded in column it converts that selling price ends with 4.99. For example, if one of my product price is 66.99 then it should converts to 64.99. Another example is if the price is 11.99 then it should converts to 14.99.

Can this be achieved in excel? Please advise.

Thanks in advance.

 

[galbert]

Hi,

You could try the following:

=NUMBERVALUE(SUBSTITUTE(B1,RIGHT(B1,4),4.99))

It assumes that the original price is in B1 and that each price ends with two decimals. It basically replaces the last 4 characters of the price with 4.99 and converts the result to a number value.

 

[denzo36]

Hey, this should get the job done

 

[ankrups]

Thank you Galbert and Denzo both formula worked. I really appreciate your response on this.

Thank you .

Ankit

 

[pgc01]

Also

=FLOOR(B1,10)+4.99