If I use a formula to attain the total created in a row, how can I then adjust the total figure so that it rounds up to the nearest 10 or 100, without losing the formula or having to enter the number in manually?

Dear Chandni

Thank you for attending Excel Intermediate course. I hope you enjoyed the course and can’t wait to put all the new topics into practice.

The answer to your question lies in a function called ROUND. Normally we cover this function in Excel 2003 Advanced level.
It is quiet straight forward. I have uploaded an Excel file for your reference. Please note that I have saved the file in a formula view but you can always change it to normal view by using the Short cut key (CTRL+`) ` key is on the bottom of the Esc key and top of the Tab key on the keyboard.

Even if you have already typed in a formula or Function e.g. SUM in your case you can apply the round Function to it.

The Syntax of the Round function is =ROUND(Number,Num_Digit)
You will notice that I have done a simple Autosum on cell D10. (Shortcut key for Autosum is ALT+=
On Cell E10 the function is =ROUND(SUM(D2:D9),-2)
So what I did is that I took the Sum function as the Number variable for my ROUND Function. Then I specified how many numbers I want it to be rounded.
It is important to note that that I used -2 (negative). The reason for doing this is that negative figure rounds the result to the left of the decimal place and Positive figure rounds the result to the right hand side of the decimal place.

I hope this has helped in answering your query.

I hope this has answered your query.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards


Rajeev Rawat
MOS Master Instructor 2000 and 2003

Attached files...

Round Function 2.xls