ceiling
 

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/comm...8f1&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

ceiling
 

=(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" wrote:

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/comm...8f1&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

ceiling
 

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

BR
/Dala

"ASA" skrev:

=(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" wrote:

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/comm...8f1&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

ceiling
 

try this one !!!

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



On Jun 3, 11:12*am, Dala wrote:
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/comm....mspx?dg=micro...

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

ceiling
 

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)



"Dala" wrote in message
...
This gave me results of *5 and *0, not *5 and *9 as wanted.
Any other options?

BR
/Dala

"ASA" skrev:

=(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" wrote:

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/comm...8f1&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

ceiling
 

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)



"Bob Phillips" wrote in message
...
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)



"Dala" wrote in message
...
This gave me results of *5 and *0, not *5 and *9 as wanted.
Any other options?

BR
/Dala

"ASA" skrev:

=(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" wrote:

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/comm...8f1&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

ceiling
 

Thank you for your help Bob.

This did the trick.

/Dala

"Bob Phillips" skrev:

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)



"Bob Phillips" wrote in message
...
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)



"Dala" wrote in message
...
This gave me results of *5 and *0, not *5 and *9 as wanted.
Any other options?

BR
/Dala

"ASA" skrev:

=(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" wrote:

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/comm...8f1&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