Results 1 to 8 of 8

20090517, 12:48 #1
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
Hi All
As in the description... it doesn't matter what the actual result would be in the cell how do I get excel to recognize if it is more than half way to the next thousand in this case to roundup?
Tia
cvbs

20090517, 14:24 #2
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='cvbs' post='775581' date='18May2009 00:48']Hi All
As in the description... it doesn't matter what the actual result would be in the cell how do I get excel to recognize if it is more than half way to the next thousand in this case to roundup?
Tia
cvbs[/quote]
try looking up ROUND in the Excel Help
your answer is in thereHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090517, 15:12 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
If I understand you correctly, you do *not* want to round down numbers ending in 001 ... 499, but you *do* want to round up numbers ending in 500 ... 999.
So 12345 > 12345 but 12789 > 13000.
With the original numbers in A1, A2 and down, you could use the following formula in B1, and fill down:
=IF(MOD(A1,1000)<500,A1,CEILING(A1,1000))

20090517, 15:57 #4
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='775596' date='17May2009 15:12']If I understand you correctly, you do *not* want to round down numbers ending in 001 ... 499, but you *do* want to round up numbers ending in 500 ... 999.
So 12345 > 12345 but 12789 > 13000.
With the original numbers in A1, A2 and down, you could use the following formula in B1, and fill down:
=IF(MOD(A1,1000)<500,A1,CEILING(A1,1000))[/quote]
Thanks to both of you for answers,
Hans, your understanding is correct as to the rounding portion of my question. The formula will work in a column as stated, however the situation I have is that the value in the "specific" cell changes the cell referenced does not change. If A1 is the cell and only the value changes and i put your formula into B1 is there a "wild card" character that I can use at the first 1000 you show to represent that no mater what the value is check and see if it is greater than 1/2 the way to the next whole 1000 number, then round up if not leave it reporting A1. Does this make any sense??
Thanks,
cvbs

20090517, 16:04 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
I'm sorry, I do not understand your question. Could you try again?

20090517, 16:08 #6
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='775607' date='17May2009 16:04']I'm sorry, I do not understand your question. Could you try again?[/quote]
Sorry, What I am trying to say is that the value in cell A1 will be constantly changing. What formula modification would I need to make in cell B1 to show roundup or remain as is.

20090517, 16:09 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Unless you turned off automatic calculation, the values in column B will automatically reflect the changes in column A.

20090517, 18:53 #8
 Join Date
 Feb 2002
 Location
 Harrisburg, Pennsylvania, USA
 Posts
 168
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='775610' date='17May2009 16:09']Unless you turned off automatic calculation, the values in column B will automatically reflect the changes in column A.[/quote]
Hans, correct my bad. Thanks again