 SOLVED

Highlighted
New Contributor

# Round of pricing

I have a spreadsheet of prices ith a mark up calculated- I want to round the number below .49c to up to .49 and the numbers between .50 and .99 up to .99 could someone please help me with how to do this- probably very basic I know

7 Replies
Highlighted

# Re: Round of pricing

Are these numbers less than 1 e.g 0.49 and 0.99? or can they take any form e.g 50.49, 60.42?

If you can send some sample data, it might be helpful

Highlighted
Best Response confirmed by Greg_M2255 (New Contributor)
Solution

# Re: Round of pricing

@Greg_M2255 This one will do it:

``=INT(A1)+(MOD(A1,1)<=0.49)*0.49+(MOD(A1,1)>0.49)*0.99``

Highlighted

# Re: Round of pricing

Thank you very much for your help @Riny_van_Eekelen

Highlighted

# Re: Round of pricing

@Greg_M2255 You're welcome!

Highlighted

# Re: Round of pricing

As variant

``=CEILING(A1+0.001,0.5)-0.01``
Highlighted

# Re: Round of pricing

From 128.49, the formula

`=INT(A1)+(MOD(A1,1)<=0.49)*0.49+(MOD(A1,1)>0.49)*0.99`

returns *.99.

e.g. 130.49 results in 130.99 which shouldn't be.

Could this be a bug or something?

@Sergei Baklan 's variant seems to be working fine though

Highlighted

# Re: Round of pricing

@wumolad Well spotted. A classic floating point error in the 15th decimal. Wrapping the MOD function in ROUND will fix it.

``=INT(A1)+(ROUND(MOD(A1,1),2)<=0.49)*0.49+(ROUND(MOD(A1,1),2)>0.49)*0.99``

Or indeed, use the more elegant solution suggested by @Sergei Baklan