Conditional round up

[AsparagusTips]

I want to round up a figure in a document. The figure needs to round up to the nearest integer but only if it is greater than or equal to x.2 and round down if less than x.2 and return a whole number only which could be any where between 0 and 500. So 3.14 rounds down and 7.26 rounds up. Any help would be appreciated as my current set of formulas uses about 7 calculations. Something easy that makes less of a mess is desired. COuld anyone help with this please?

 

[PichaiTC]

Let try this

=ROUND(A1+0.3,0)

 

[DILIPandey]

Okay... try using below formula which I have tested on cell A1:-


=IFERROR(IF(MID(A1,SEARCH(".",A1),15)*1<0.2,LEFT(A1,SEARCH(".",A1)-1)*1,(LEFT(A1,SEARCH(".",A1)-1)+1)*1),A1)



Regards,
DILIPandey

 

[AsparagusTips]

Okay... try using below formula which I have tested on cell A1:-


=IFERROR(IF(MID(A1,SEARCH(".",A1),15)*1<0.2,LEFT(A1,SEARCH(".",A1)-1)*1,(LEFT(A1,SEARCH(".",A1)-1)+1)*1),A1)



Regards,
DILIPandey

You Sir, DILIPandey, are amazing. Thank you, that worked fantastically! My colleagues are amazed.

 

[DILIPandey]

You are welcome AsparagusTips.... Cheers


Regards,
DILIPandey