Round up & down in single formula

Hi Avk,

Simple follow the following steps.

you can simply do it without formula by following steps. Select your range which you wanted to convert into without decimal, then do right click or press CTRL+1 , and FORMAT CELLS window will appear. Refer the inline image



Format Cell 3.png

Or you Can do it by going into Home tab --> Number (See below image) --> Click 2 times on Decrease Decimal

Format Cell 2.png

The above customize formatting will automatically roundup your number if numbers are greater than 50 after decimal and in same ways it rounddown if numbers are less than 50 after decimal.

I hope this is what you were looking for.

Hi Avk,

You can use Round function. Lets say You have value 1685.49 in A1 then formula will be =round(A1) and it will return it to 1685 and if you have 1686.51 then it will return it to 1686.

Hope this make works for you.

Originally Posted by avk

Suppose in cell B1 value is 1685.49 then result show as 1685.00 (Rounddown) but if in cell value is 1685.51 then result show as 1686.00

It sounds like you simply want the ROUND function, to wit: =ROUND(B1,0).

But what if the value is 1684.5 and 1685.5. ROUND will return 1685 and 1686 respectively. But some people what 1684 and 1686 -- so-called banker's rounding.

For banker's rounding, you might write: =ROUND(B1,0)-(MOD(B1,1)=0.5).

[ERRATA] The formula should be:
=ROUND(B1,0) - IF(ISEVEN(INT(B1)),MOD(B1,1)=0.5)

[EDIT] Unfortunately, there is a defect (IMHO) in Excel ROUND. For example, if B1 is =1685.5-"4E-12", that should round down to 1685 because 1685.5-"4E-12" is less than 1685.5. But instead, it rounds to 1686 because MOD(B1,1)<>0.5, but ROUND(B1,0) incorrectly rounds to 1686. That might seem okay to you because Excel displays 1685.50000000000 if B1 is formatted to 11 decimal places, the most that Excel formats, an arbitrary limit. But you can use =B1-1685.5<0 to confirm that 1685.5-"4E-12" is less than 1685.5.

FYI, =B1<1685.5 returns FALSE incorrectly because of a dubious heuristic in Excel that treats different values as equal if they are "close enough".

Originally Posted by joeu2004

It sounds like you simply want the ROUND function, to wit: =ROUND(B1,0).

But what if the value is 1684.5 and 1685.5. ROUND will return 1685 and 1686 respectively. But some people what 1684 and 1686 -- so-called banker's rounding.

Ostensibly, you might write: =ROUND(B1,0)-(MOD(B1,1)=0.5).

Yup you are right, But I think he is not looking for to have Round to return 1685 for value 1685.50 as he must be looking for 1686 for 1685.5 (As in accounting it always vary if you even have 1 after decimals)


Waiting for AVK's answer

[.... deleted ....]