Rounding Numbers using a certain condition

Question

There are numbers in Column A of the attached excel with decimal points. Can we using a single formula, achieve the result in "Output Desired" column. I want to integrate two conditions- if there is a number less than 1 or equal to 1, for eg. 0.002,0.5,1 , output should be 1. For others numbers like greater than 1, it should be rounded down to the lowest integer. For eg. 1.6 should be 1, 6.3 should render an output as 6. Can someone help me in integrating this in one formula please? Thanks a lot!

Rounding values.xlsx

afv2688 · Accepted Answer

Hello @B_bhatta ,

the formula you should use would be this one:

IIF(ABS([Field1])<1, 1, FLOOR([Field1]))

I've added the ABS just in case the same rule has to be applied for negative numbers

Regards

Qiu · Accepted Answer

@B_bhatta 
Floor Function is what we need, similar with @afv2688