SOLVED

Round of pricing

Copper Contributor

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

Hi @Greg_M2255 

 

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?

 

Please clarify.

 

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

best response confirmed by Greg_M2255 (Copper Contributor)
Solution

@Greg_M2255 This one will do it:

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

 

Thank you very much for your help @Riny_van_Eekelen 

@Greg_M2255 You're welcome!

@Greg_M2255 

As variant

=CEILING(A1+0.001,0.5)-0.01

@Riny_van_Eekelen @Greg_M2255 @Sergei Baklan 

 

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

@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 

1 best response

Accepted Solutions
best response confirmed by Greg_M2255 (Copper Contributor)
Solution

@Greg_M2255 This one will do it:

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

 

View solution in original post