| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can you round a number to the nearest 900?
what we are looking for is to round to the nearest 900. Example: if you have
$100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Try this
=MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Surely MROUND(A1,900)
And you need Analysis Toolpac installed (unless we are taking XL2007) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mike H" wrote in message ... Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Surely no because that would round to a multiple of 900 so
100,500 rounds to 100,800 which isn't my understanding of what the OP asked for Mike "Bernard Liengme" wrote: Surely MROUND(A1,900) And you need Analysis Toolpac installed (unless we are taking XL2007) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mike H" wrote in message ... Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
MRound only exists as a funtion if you have the analysis toolpak installed.
If it is not installed you get a #Value error. Instead of MRound you can just use round something like this round(A1, -3) which is the same as mround(A1, 1000) Now you do not need the analysis toolpak So the final formula would be... =round(A1, -3) - 100 -- HTH... Jim Thomlinson "Mike H" wrote: Surely no because that would round to a multiple of 900 so 100,500 rounds to 100,800 which isn't my understanding of what the OP asked for Mike "Bernard Liengme" wrote: Surely MROUND(A1,900) And you need Analysis Toolpac installed (unless we are taking XL2007) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mike H" wrote in message ... Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Jim,
Thanks for that but with the benefit of Joel's post I can see my solution fails for 100401 Mike "Jim Thomlinson" wrote: MRound only exists as a funtion if you have the analysis toolpak installed. If it is not installed you get a #Value error. Instead of MRound you can just use round something like this round(A1, -3) which is the same as mround(A1, 1000) Now you do not need the analysis toolpak So the final formula would be... =round(A1, -3) - 100 -- HTH... Jim Thomlinson "Mike H" wrote: Surely no because that would round to a multiple of 900 so 100,500 rounds to 100,800 which isn't my understanding of what the OP asked for Mike "Bernard Liengme" wrote: Surely MROUND(A1,900) And you need Analysis Toolpac installed (unless we are taking XL2007) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mike H" wrote in message ... Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
The functions in the analysis toolpak have been moved into excel proper in
xl2007. Jim Thomlinson wrote: MRound only exists as a funtion if you have the analysis toolpak installed. If it is not installed you get a #Value error. Instead of MRound you can just use round something like this round(A1, -3) which is the same as mround(A1, 1000) Now you do not need the analysis toolpak So the final formula would be... =round(A1, -3) - 100 -- HTH... Jim Thomlinson "Mike H" wrote: Surely no because that would round to a multiple of 900 so 100,500 rounds to 100,800 which isn't my understanding of what the OP asked for Mike "Bernard Liengme" wrote: Surely MROUND(A1,900) And you need Analysis Toolpac installed (unless we are taking XL2007) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mike H" wrote in message ... Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Hi, Mike!
Thanks so much! That worked out great. I have one more question. How would I do the same thing, but round to the nearest 500? "Mike H" wrote: Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
My 900 solution will fail for certain numbers, have a look at Joel's answer
but for a ((hopefully) less contreversial 500 try this =MROUND(A1,500) Mike "trainer07" wrote: Hi, Mike! Thanks so much! That worked out great. I have one more question. How would I do the same thing, but round to the nearest 500? "Mike H" wrote: Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
in regards to rounding to nearest 500, here are some examples...
$100,400 round to $100,500.....$100,800 round to 101,000.....100,749 round to 100,500..... "Mike H" wrote: Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Something like this...
=ROUND(A1/500, 0) * 500 this avoids the use of the analysis toolpak. With the toolpak =mround(A1, 500) Where possible I avoid formulas that require the toolpak. If you send th sheet to someone without the toolpak they can get #value errors... -- HTH... Jim Thomlinson "trainer07" wrote: in regards to rounding to nearest 500, here are some examples... $100,400 round to $100,500.....$100,800 round to 101,000.....100,749 round to 100,500..... "Mike H" wrote: Try this =MROUND(A1,1000)-100 Mike "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
=ROUND(A30/900,0)*900
but not 100,500 rounds to 100,800 -- if must be a multiple or 900 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "trainer07" wrote in message ... what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Typo: Not 100,900 but 100,800 !!!
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... =ROUND(A30/900,0)*900 but not 100,500 rounds to 100,800 -- if must be a multiple or 900 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "trainer07" wrote in message ... what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
You need to play some tricks. Module arithmetic only works if you add 100 to
the number and then divide by 1000. Ignore my previous solution. =IF(MOD((A1+100),1000)<=500,1000*(INT((A1+100)/1000)-1)+900,1000*(INT((A1+100)/1000))+900) "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Joel,
nice solution Mike "Joel" wrote: You need to play some tricks. Module arithmetic only works if you add 100 to the number and then divide by 1000. Ignore my previous solution. =IF(MOD((A1+100),1000)<=500,1000*(INT((A1+100)/1000)-1)+900,1000*(INT((A1+100)/1000))+900) "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Would you want 100,400 to round up to 100,900 or down to 99,900?
"trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
Here's a version that seems to work. It rounds up from 400 and down from 399.
=(INT((G13-400)/1000)*1000)+900 "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can you round a number to the nearest 900?
I think you're onto something, Ed! It appears that you can expand your
formula to calculate any value, v, to the nearest integer, n, between 100 & 999: =(INT((v+500-n)/1000)+n So to the nearest 900: =INT((v+500-900)/1000)+900 or =INT((v-400)/1000)+900 ...same as your post. To the nearest 300: =INT((v+500-300)/1000)+300 or =INT((v+200)/1000)+300 "Ed Cones" wrote: Here's a version that seems to work. It rounds up from 400 and down from 399. =(INT((G13-400)/1000)*1000)+900 "trainer07" wrote: what we are looking for is to round to the nearest 900. Example: if you have $100,500 we want it rounded to $100,900 If we have $100,200 we want it rounded to $99,900 Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Round a number to the Nearest .25 | Excel Worksheet Functions | |||
| How do I round a number to the nearest 900? | Excel Discussion (Misc queries) | |||
| Is there a formula to round a number to the nearest .09? | Excel Worksheet Functions | |||
| How do I round a 4 digits number to it's nearest 10 | Excel Worksheet Functions | |||
| want to round a number to the nearest quarter 0.25-0.50-0.45-0.00 | Excel Worksheet Functions | |||
Hybrid Mode
