A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

MOD and Ceiling formula used to round up and down to 49 and 99



 
 
Thread Tools Display Modes
  #1  
Old September 11th 08, 08:54 PM posted to microsoft.public.excel.misc
Angie33
external usenet poster
 
Posts: 8
Default 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 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
Ads
  #2  
Old September 12th 08, 12:10 AM posted to microsoft.public.excel.misc
Sheeloo
external usenet poster
 
Posts: 795
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

  #3  
Old September 15th 08, 12:59 PM posted to microsoft.public.excel.misc
Angie33
external usenet poster
 
Posts: 8
Default 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 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

  #4  
Old September 15th 08, 03:48 PM posted to microsoft.public.excel.misc
Angie33
external usenet poster
 
Posts: 8
Default 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 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Roundup or Ceiling Function to round to a specific number Angie33 Excel Discussion (Misc queries) 26 September 11th 08 04:29 PM
ceiling Dala Excel Discussion (Misc queries) 6 June 3rd 08 11:13 AM
how to use the ceiling function Larry Excel Worksheet Functions 2 April 11th 07 07:34 PM
Round/Ceiling on an IF function returning numerical value or text donnaK Excel Worksheet Functions 4 December 13th 05 04:20 PM
Max, Ceiling, If, Etc Chris W via OfficeKB.com Excel Discussion (Misc queries) 3 November 22nd 05 09:40 PM


All times are GMT +1. The time now is 09:01 AM.


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