How to round the numeric value?

  • Thread starter Thread starter Salman Saeed
  • Start date Start date
S

Salman Saeed

Kindly tell me that how can we round the numeric value?
Example: 1.2 = 1.5, 1.7 = 2.0
 
Mon, 26 Apr 2010 02:59:01 -0700 from =?Utf-8?B?U2FsbWFuIFNhZWVk?= <=?
Utf-8?B?U2FsbWFuIFNhZWVk?=>:
Kindly tell me that how can we round the numeric value?
Example: 1.2 = 1.5, 1.7 = 2.0
Click to expand...

I don't know what that is, but it's not rounding. If you're rounding
to the nearest whole, 1.2 would round to .0 not 1.5. Even if you are
rounding to the nearest half, 1.7 would round to 1.5.

Instead of just giving examples, please state your what you're
actually trying to accomplish.
 
Stan Brown said:
Mon, 26 Apr 2010 02:59:01 -0700 from =?Utf-8?B?U2FsbWFuIFNhZWVk?= <=?
Utf-8?B?U2FsbWFuIFNhZWVk?=>:

I don't know what that is, but it's not rounding. If you're rounding
to the nearest whole, 1.2 would round to .0 not 1.5. Even if you are
rounding to the nearest half, 1.7 would round to 1.5.
Click to expand...

The OP could certainly use ROUNDUP on the 1.7 value, but the only way of
making 1.2 show as 1.5 (AFAIK) would be to use an IF statement..
 
In addition to what ozgrid.com provided (which will round UP to the next
nearest .5 boundary), look at
=MROUND(1.2,0.5)
Since MROUND() rounds up,the effect in this case is the same as
CIELING(1.2,0.5). Plus, MROUND() requires that the Analysis ToolPak add-in
be active/installed.

Just another alternative. You can also look at FLOOR() if you need to round
DOWN.
 
I think you need =ROUND(A2/0.5,0)*0.5
Here are examples of results from CEIL, MROUND and ROUND
number =CEILING(A2,0.5) =MROUND(A2,0.5) =ROUND(A2/0.5,0)*0.5
1.45 1.5 1.5 1.5
1.5 1.5 1.5 1.5
1.7 2 1.5 1.5
1.9 2 2 2
2 2 2 2
2.1 2.5 2 2
2.45 2.5 2.5 2.5
2.5 2.5 2.5 2.5

best wishes
 
JLatham said:
look at =MROUND(1.2,0.5)
Since MROUND() rounds up
Click to expand...

I don't think so.
the effect in this case is the same as
CIELING(1.2,0.5).
Click to expand...

I don't think so.

MROUND(1.2,0.5) is 1, whereas CEILING(1.2,0.5) is 1.5.

MROUND rounds; CEILING rounds up. Since the latter provides the result that
Saeed requested (based on his two examples), CEILING is the correct function
to use.

Of course, that begs the questions of whether the two examples sufficiently
demonstrate the kind of rounding that Saeed really wants, and whether his two
examples correctly reflect what he wants. I would like to have seen more
examples, notably 1.1 and 1.6.


----- original message -----
 
Bernard Liengme said:
I think you need =ROUND(A2/0.5,0)*0.5 [....]
number =CEILING(A2,0.5) =MROUND(A2,0.5) =ROUND(A2/0.5,0)*0.5 [....]
1.7 2 1.5 1.5
Click to expand...

But Saeed said that 1.7 should become 2. So by your own examples, CEILING
is the only one of those 3 formulas that works for both examples that Saeed
gave. (However, Saeed might have misrepresented his requirements.)

Of course, Saleed could use ROUNDUP(A1*2,0)/2, which should be functionally
equivalent to CEILING(A1,0.5).

I say "should be" because with Excel, one can never be sure. Sh*t happens!
;-)


----- original message -----
 
Quite right, thanks for the correction. I'd actually thought MRound()
rounded, but read something in Help that led me in the other direction -
quite possibly I was looking at CEILING() help and thinking MROUND().

As for what the OP really wants? Who knows, I think everyone is doing the
typical, reasonable thing -- interpreting based on example data/results
provided, which is often quite full of holes.
 
Back
Top