Thread: specific rounding of formulas which may need to include an IF stat

I am currently working on a worksheet where i would like to round the value
in a cell (the cells currently contain a formula which has been rounded to 0
decimal places). The rule being that if the last digit (before the decimal
place) is the number 0, 1,2,3 or 4 then round up the last digit to the digit
5. If the number is 5,6,7,8 or 9 then round up the last digit to 9. I would
like to know how to write this formula.

For example
if the value is 493.75 then round up to 495.
if the value is 498.75 then round up to 499.
if the value is 1233.75 then round up to 1235.
if the value is 1238.75 then round up to 1239.

One way:

=CEILING(INT(A1)+0.1,5)-(MOD(A1,10)>=5)

In article <0E9E62B3-FC69-4178-843C-02FB315EA244@microsoft.com>,
"Gerry Wilkins" <Gerry Wilkins@discussions.microsoft.com> wrote:

> I am currently working on a worksheet where i would like to round the value
> in a cell (the cells currently contain a formula which has been rounded to 0
> decimal places). The rule being that if the last digit (before the decimal
> place) is the number 0, 1,2,3 or 4 then round up the last digit to the digit
> 5. If the number is 5,6,7,8 or 9 then round up the last digit to 9. I would
> like to know how to write this formula.
>
> For example
> if the value is 493.75 then round up to 495.
> if the value is 498.75 then round up to 499.
> if the value is 1233.75 then round up to 1235.
> if the value is 1238.75 then round up to 1239.

Hi,

Here is one solution:
=IF((A1-TRUNC(A1/10,0)*10)<5,5,9)+TRUNC(A1/10,0)*10

Ola

Thanks for the above solution. I'm also need a solution alomost like the above. So I put my query here.

For example
if the value is 101 or 102 then round up to 100.

if the value is 103 or 104 then round up to 105
if the value is 106 or 107 then round up to 105

if the value is 108 or 109 then round up to 110

Thanks in advance!

Originally Posted by mikerickson

For Lavan_Joy,
=CEILING(A1-2, 5)

Thanks a lot Rickson! You have solved my problem!