Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    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

  2. #2
    3 Star Lounger
    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='18-May-2009 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 there
    Hope 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

  3. #3
    Plutonium Lounger
    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))

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775596' date='17-May-2009 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

  5. #5
    Plutonium Lounger
    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?

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775607' date='17-May-2009 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.

  7. #7
    Plutonium Lounger
    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.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775610' date='17-May-2009 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •