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


You can apply an IF statement



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.



@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.

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
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



Press Ctrl+1


Then follow picture


Decimal Number must be 0


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