Roundup

%3CLINGO-SUB%20id%3D%22lingo-sub-1902955%22%20slang%3D%22en-US%22%3ERoundup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1902955%22%20slang%3D%22en-US%22%3EI%20will%20have%20number%20as%20flows%201.2%203.7%206.1%20thing%20is%20I%20can%20roundup%20these%20numbers%20to%20the%20nearest%20hole%20number%20but%20my%20problem%20is.%20I%20will%20also%20have%20numbers%20like%20-0.4%20-3.1%20-6%20when%20I%20do%20the%20roundup%20formula%20the%20-%20numbers%20round%20up%20to%20highest%20-%20number%20were%20i%20need%20them%20just%20to%20show%20it%20as%200%20or%20even%20better%20show%20no%20number%20at%20all%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1902955%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor
I will have number as flows 1.2 3.7 6.1 thing is I can roundup these numbers to the nearest hole number but my problem is. I will also have numbers like -0.4 -3.1 -6 when I do the roundup formula the - numbers round up to highest - number were i need them just to show it as 0 or even better show no number at all. I know I can use rounddown but I'm looking for a fourmla that does them both together.. hope iv giving enough info..
7 Replies
Highlighted

@Norman_Glenn 

You can apply an IF statement

=IF(number>0,ROUNDUP(number,),)

Highlighted

@Norman_Glenn 

Hi there,

To be honest, I don't understand your query. However, I tried to solve this issue as I understood using Round Function.

 

And it worked well.

 

Please note attached file.

 

If you can clarify your question a little bit more, I can help you.

 

 

Highlighted

@Norman_Glenn The function CEILING()  or CEILING.MATH() is what you want, but you also mention that negative number you want to be 0 so you can use MAX(CEILING(A1,1),0) so either A1 is rounded up to the next integer if positive or it is set to 0 if negative.

Highlighted
Round does not work for me as if a number is 1.2 it rounds the number to1.. I need to use roundup so it rounds the number to next highest number witch would 2. I will have a long colum with random numbers like 1.2 4.7 2.5 and so on...round up will round them up to next hole number like 2 5 3.... amoungest these numbers their will be negative numbers like -2.4 -1.6 -8.1 when I us roundup these numbers change to -3 -2 -9 I need these native numbers to change to 0
Highlighted
Thank you for you response. That fourmla works great.. if you could please help with one more thing.. in the fourmla can I add in something. My numbers are showing up after using your fourmla as 2.0 3.0 8.0 0.0 can the fourmla be edited so the numbers are showing like 2 3 8 0
Highlighted

@Norman_Glenn 

 

Press Ctrl+1

 

Then follow picture

Ilgar_Zarbaliyev_0-1605819840048.png

Decimal Number must be 0

Highlighted

@Norman_Glenn 

Glad to hear it, a shortcut to adjust decimal placements is available in the Home ribbon. 

adversi_0-1605820940153.png