# Thread: Round up and down (Excel 2000)

1. ## 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. ## 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. ## 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>

4. ## 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. ## 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. ## 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. ## 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.

Ok.

9. ## 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. ## Re: Round up and down (Excel 2000)

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

11. ## 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. ## 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
•