Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    Phoenx, Arizona, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Round up and down (Excel 2000)

    I'm fairly new to Excel, so please pardon my childish question.

    How do I round numbers up and down based on a particular criterior? I have a score sheet that I'd like to adjust the final scores to. For example, if the total score is 1.5 or greater, I'd like it to round up to 2.0. Similarly, if the number is less than 1.5, I'd like to round it down to 1.0.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round up and down (Excel 2000)

    If it's just a matter of how the numbers are displayed, select the cells, then select Format | Cells..., and set the number format to a format with 0 decimals.

    If you want rounded values, you can use the ROUND worksheet function. Say that the scores are in (for example) G1:G10. Select H1, and enter the formula =ROUND(G1,0). Fill down this formula to cell H10, for example by selecting H1 again (if necessary) and double clicking the fill handle - the little black square in the lower right corner of the cell.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round up and down (Excel 2000)

    If the final score is in A1, then a formula like this in another cell would do what you asked:

    <pre>=IF(A1<1.5,ROUNDDOWN(A1,0),ROUNDUP(A1,0))
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Location
    Phoenx, Arizona, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round up and down (Excel 2000)

    This worked great, thanks. However, my final result will only show up if I format the cells to "text", but I would like them to show up as a "number" with 2 decimal places. When I tried to format the result to a number, a "##" sign showed up.

    The funny thing is when I (through AutoSum) copied this info onto a separate recap worksheet, the results showed up correctly as numbers with 2 decimal places. The only problem I'm having now is on the original worksheet with the original rounded numbers.

    Any suggestions?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round up and down (Excel 2000)

    >> When I tried to format the result to a number, a "##" sign showed up.

    Perhaps the column was too narrow to display the number complete with the decimals. Try making the column wider.

  6. #6
    New Lounger
    Join Date
    Sep 2003
    Location
    Phoenx, Arizona, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round up and down (Excel 2000)

    Did that and it didn't work. The "#" sign is actually on the lines that separate the rows.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round up and down (Excel 2000)

    I have no idea what that could be. Could you attach a small screenshot, or a small demo spreadsheet? We don't need the entire workbook you're working on, just a small extract that demonstrates the problem.

  8. #8
    New Lounger
    Join Date
    Sep 2003
    Location
    Phoenx, Arizona, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round up and down (Excel 2000)

    Ok.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round up and down (Excel 2000)

    Check your alignment and format. When I examined it the numbers in the Round Cells the numbers run vertically, not horizontally. Select cells, Format, Alignment and change the angle, Click OK and OK, then repeat again to bring the text into proper alignment.

  10. #10
    New Lounger
    Join Date
    Sep 2003
    Location
    Phoenx, Arizona, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round up and down (Excel 2000)

    Thank you, that worked perfectly! It's usually the simplest answers that confuse us the most!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round up and down (Excel 2000)

    Thanks, I wouldn't have guessed this. The cells in column M have been set for vertical text orientation. Select M2:M15, then select Format | Cells, activate the Alignment (?) tab, and click the vertical word "Text" in the Orientation (?) frame. Finally click OK.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Round up and down (Excel 2000)

    For some reason the cells that were giving you trouble had been formatted as "vertically aligned text" - usually this is used for text labels on charts and such. The problem wasn't that the cells weren't wide enough, but that they were not tall enough. Resetting to horizontal alignment fixes the prblem, as shown i the attached s/sheet.

    Click on Format | Cells and then on the 'alignment' tab to see the different settings

Posting Permissions

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