Apr 29 2020 07:00 PM - edited Apr 29 2020 07:01 PM
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
Apr 29 2020 07:27 PM
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
Apr 29 2020 09:06 PM
SolutionApr 30 2020 08:01 AM
@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
Apr 30 2020 10:16 PM
@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
Apr 29 2020 09:06 PM
Solution