| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to use the ceiling function | Excel Worksheet Functions | |||
| Cumulative sum with a ceiling | Excel Worksheet Functions | |||
| Max, Ceiling, If, Etc | Excel Discussion (Misc queries) | |||
| Something like CEILING or FLOOR | Excel Worksheet Functions | |||
| ceiling & floor | New Users to Excel | |||
Linear Mode
