ceiling

[Dala]

Hi.

I found this in a post when searching.
The solution was provided by Bob Phillips back in 2006.

=CEILING(A10,5)-(AND(MOD(A10,10)<>0,MOD(CEILING(A10,5),10)=0))

What it does, is to round up to a 5, 9 or 0, depending on the data.
This was exactly what was needed for that post in 2006.
Example
11 = 15
14 = 15
16 = 19
18 = 19
19 = 19
20 = 20
21 = 25

I need the same thing, but not when it comes to *0 = *0

What I need is
11 = 15
14 = 15
16 = 19
18 = 19
19 = 19
20 = 25
21 = 25

Bobs explanation can be found at

http://www.microsoft.com/office/com...026226-880a-4439-8736-ad2dba83c8f1&sloc=sv-se

Is it possible that someone could modify this formula, or perhaps suggest
another one for me?
I seem to have no luck.

Grateful for any help!

BR

/Dala

 

[ASA]

=(INT(A1/5)+1)*5 where A1 contains your value

or =(INT(A1/$b$1)+1)*$b$1 where $b$1 will contain your rounding factor.

 

[Dala]

This gave me results of *5 and *0, not *5 and *9 as wanted.
Any other options?

BR
/Dala

"ASA" skrev:

 

[muddan madhu]

try this one !!!

=IF(MOD(H23,10)>0,CEILING(H23,5)-
(AND(MOD(H23,10)<>10,MOD(CEILING(H23,5),10)=0)),INT((H23/5)+1)*5)

 

[Bob Phillips]

Dala,

What should 15, 25 become, 15 or 19, 25 or 29?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Assuming 15 and 25,

=CEILING(A1,5)-(MOD(CEILING(A1,5),10)=0)+(MOD(A1,10)=0)*6

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dala]

Thank you for your help Bob.

This did the trick.

/Dala

"Bob Phillips" skrev: