how to use function ceiling??

  • Thread starter Thread starter keys2000
  • Start date Start date
K

keys2000

got a problem with function ceiling, my formula is =ceiling(A1,1)
A1 = 15
my result is = 15
but if i change my A1 to = 15.01
my result is = 16
but i want my result like this
if A1 = 15.01 --> 15.09, i want my result to be 15
but if my A1 = 15.1 and above, i want my result to be 16
 
This is what the help says..
Returns number rounded up, away from zero, to the nearest multiple of
significance. For example, if you want to avoid using pennies in your prices
and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to
round prices up to the nearest nickel.

Syntax

CEILING(number,significance)

Number is the value you want to round.

Significance is the multiple to which you want to round.

So
=Ceiling(15.01,0.1) will round to multiple of 0.1 giving you 15.1

You need an IF to apply two different ceiling functions depending upon
whether less than 15.1 or mor..
 
CEILING() rounds a number to the nearest integer or to the nearest multiple
of significance. Refer help for a much detailed explanasion
CEILING(15.08,0.1) will result in 15.1

Here since your condition is to roundown if less than or equal to .09. Use
ROUNDDOWN and ROUNDUP functions

=IF(MOD(A1,1)<=0.09,ROUNDDOWN(A1,),ROUNDUP(A1,))


If this post helps click Yes
 
got a problem with function ceiling, my formula is =ceiling(A1,1)
A1 = 15
my result is = 15
but if i change my A1 to = 15.01
my result is = 16
but i want my result like this
if A1 = 15.01 --> 15.09, i want my result to be 15
but if my A1 = 15.1 and above, i want my result to be 16
Click to expand...

You don't write what you want to happen is 15.09<A1<15.1

Something like:

=CEILING(A1-0.09,1)

will do what you write, but also roundup anything greater than 15.09.

If, for example, you would want 15.095 to round to 15 instead of 16, increase
the precision of the subtrahend to that necessary:

e.g: =CEILING(A1-0.099,1)
--ron
 
Back
Top