SOLVED

Round of pricing

%3CLINGO-SUB%20id%3D%22lingo-sub-1348573%22%20slang%3D%22en-US%22%3ERound%20of%20pricing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1348573%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20of%20prices%20ith%20a%20mark%20up%20calculated-%20I%20want%20to%20round%20the%20number%20below%20.49c%20to%20up%20to%20.49%20and%20the%20numbers%20between%20.50%20and%20.99%20up%20to%20.99%20could%20someone%20please%20help%20me%20with%20how%20to%20do%20this-%20probably%20very%20basic%20I%20know%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1348573%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1348605%22%20slang%3D%22en-US%22%3ERe%3A%20Round%20of%20pricing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1348605%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F642260%22%20target%3D%22_blank%22%3E%40Greg_M2255%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20these%20numbers%20less%20than%201%20e.g%200.49%20and%200.99%3F%20or%20can%20they%20take%20any%20form%20e.g%2050.49%2C%2060.42%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20clarify.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20send%20some%20sample%20data%2C%20it%20might%20be%20helpful%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1348708%22%20slang%3D%22en-US%22%3ERe%3A%20Round%20of%20pricing%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1348708%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F642260%22%20target%3D%22_blank%22%3E%40Greg_M2255%3C%2FA%3E%26nbsp%3BThis%20one%20will%20do%20it%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DINT(A1)%2B(MOD(A1%2C1)%26lt%3B%3D0.49)*0.49%2B(MOD(A1%2C1)%26gt%3B0.49)*0.99%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

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

Highlighted
Best Response confirmed by Greg_M2255 (New 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

 

Highlighted

Thank you very much for your help @Riny_van_Eekelen 

Highlighted

@Greg_M2255 You're welcome!

Highlighted

@Greg_M2255 

As variant

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

@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

Highlighted

@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