Round up and down (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Did that and it didn’t work. The “#” sign is actually on the lines that separate the rows.

Reply | Quote

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.

Reply | Quote

Ok.

Reply | Quote

Ok.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Did that and it didn’t work. The “#” sign is actually on the lines that separate the rows.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

=IF(A1<1.5,ROUNDDOWN(A1,0),ROUNDUP(A1,0))

Reply | Quote

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

=IF(A1<1.5,ROUNDDOWN(A1,0),ROUNDUP(A1,0))

Reply | Quote