View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Sheeloo Sheeloo is offline
external usenet poster
 
Posts: 793
Default 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 1-28 it could round down to 99. If its 29-48 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