#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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




  #2   Report Post  
Posted to microsoft.public.excel.misc
ASA ASA is offline
external usenet poster
 
Posts: 13
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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








  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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








  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to use the ceiling function Larry Excel Worksheet Functions 2 April 11th 07 07:34 PM
Cumulative sum with a ceiling Max Excel Worksheet Functions 6 January 2nd 07 01:34 AM
Max, Ceiling, If, Etc Chris W via OfficeKB.com Excel Discussion (Misc queries) 3 November 22nd 05 10:40 PM
Something like CEILING or FLOOR gusvenables Excel Worksheet Functions 3 October 28th 05 04:09 AM
ceiling & floor Bill Ridgeway New Users to Excel 1 August 7th 05 02:32 PM


All times are GMT +1. The time now is 10:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"