Home 
Search 
Today's Posts 
#1




MOD and Ceiling formula used to round up and down to 49 and 99
Someone name Bob Phillips created this formula for me to have my numbers end
in either a 5 or a 9 and it works great. =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0)) Can this formula be altered to end in either 49 or 99, depending on where the numbers fall. Example 4419 rounded down to 3999, 3329 rounded down to 3299 or 3339 rounded up to 3349, 3359 rounded up to 3399. I guess if the numbers were from 128 it could round down to 99. If its 2948 round up to 49. If 50 to 59 then round down to 49 and if its 65 and up round up to 99. Any assistance I can get with this would be great. Thank you.  Angie33 "Bob Phillips" wrote: =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))  Angie33 
#2




MOD and Ceiling formula used to round up and down to 49 and 99
Try
=CEILING(A1,50)(AND(MOD(A1,50)<0,MOD(CEILING(A1,50),50)=0)) and let me know... Results with this (Col b) and your old formula (Col c) are copied below; Number New Formula Old Formula 4419 4449 4419 3329 3349 3329 3339 3349 3339 3359 3399 3359 4418 4449 4419 4414 4449 4415 4411 4449 4415 11 49 15 10 49 10 9 49 9 106 149 109 "Angie33" wrote: Someone name Bob Phillips created this formula for me to have my numbers end in either a 5 or a 9 and it works great. =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0)) Can this formula be altered to end in either 49 or 99, depending on where the numbers fall. Example 4419 rounded down to 3999, 3329 rounded down to 3299 or 3339 rounded up to 3349, 3359 rounded up to 3399. I guess if the numbers were from 128 it could round down to 99. If its 2948 round up to 49. If 50 to 59 then round down to 49 and if its 65 and up round up to 99. Any assistance I can get with this would be great. Thank you.  Angie33 "Bob Phillips" wrote: =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))  Angie33 
#3




MOD and Ceiling formula used to round up and down to 49 and 99
Sheeloo, I am going to give this a try and I will let you know how it works,
thank you.  Angie33 "Sheeloo" wrote: Try =CEILING(A1,50)(AND(MOD(A1,50)<0,MOD(CEILING(A1,50),50)=0)) and let me know... Results with this (Col b) and your old formula (Col c) are copied below; Number New Formula Old Formula 4419 4449 4419 3329 3349 3329 3339 3349 3339 3359 3399 3359 4418 4449 4419 4414 4449 4415 4411 4449 4415 11 49 15 10 49 10 9 49 9 106 149 109 "Angie33" wrote: Someone name Bob Phillips created this formula for me to have my numbers end in either a 5 or a 9 and it works great. =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0)) Can this formula be altered to end in either 49 or 99, depending on where the numbers fall. Example 4419 rounded down to 3999, 3329 rounded down to 3299 or 3339 rounded up to 3349, 3359 rounded up to 3399. I guess if the numbers were from 128 it could round down to 99. If its 2948 round up to 49. If 50 to 59 then round down to 49 and if its 65 and up round up to 99. Any assistance I can get with this would be great. Thank you.  Angie33 "Bob Phillips" wrote: =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))  Angie33 
#4




MOD and Ceiling formula used to round up and down to 49 and 99
I tried the formula and it does work, but I still need to be able to divide
to get my new number not just round up to the 49 or 99. I need to be able get the number then have it round up to the 49 or 99. This is my old formula. =CEILING(ROUND(H3/0.8,0),5)(MOD(CEILING(ROUND(H3/0.8,0),5),10)=0)  Angie33 "Sheeloo" wrote: Try =CEILING(A1,50)(AND(MOD(A1,50)<0,MOD(CEILING(A1,50),50)=0)) and let me know... Results with this (Col b) and your old formula (Col c) are copied below; Number New Formula Old Formula 4419 4449 4419 3329 3349 3329 3339 3349 3339 3359 3399 3359 4418 4449 4419 4414 4449 4415 4411 4449 4415 11 49 15 10 49 10 9 49 9 106 149 109 "Angie33" wrote: Someone name Bob Phillips created this formula for me to have my numbers end in either a 5 or a 9 and it works great. =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0)) Can this formula be altered to end in either 49 or 99, depending on where the numbers fall. Example 4419 rounded down to 3999, 3329 rounded down to 3299 or 3339 rounded up to 3349, 3359 rounded up to 3399. I guess if the numbers were from 128 it could round down to 99. If its 2948 round up to 49. If 50 to 59 then round down to 49 and if its 65 and up round up to 99. Any assistance I can get with this would be great. Thank you.  Angie33 "Bob Phillips" wrote: =CEILING(A10,5)(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))  Angie33 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Roundup or Ceiling Function to round to a specific number  Excel Discussion (Misc queries)  
ceiling  Excel Discussion (Misc queries)  
how to use the ceiling function  Excel Worksheet Functions  
Round/Ceiling on an IF function returning numerical value or text  Excel Worksheet Functions  
Max, Ceiling, If, Etc  Excel Discussion (Misc queries) 