How to Round Up in Excel

Jun 28, 2020 ยท 6 minute read

If we have numbers in our table and we have to limit the decimals, we can use these commands.

How to Round Number in Excel.

In this command, if your integrated or decimal part is less than 5, you will be rounded to a smaller value, and if it is equal to or greater than 5, it will be rounded to a larger amount.

Here we want to explain this command with an example.

In this table, we have a salary of the employees in one company in this year and we want to calculate the amount of their salary in the next year if their salaries increase 7.3 per cent in the next year.

In column C, we have the salaries of employees in this company in the current year, in column “D” we have the salary of the employees in the new year, we want to round the new salaries in a penny in Column “E”, in column “G” we want to round the salaries into the dollar and, we want to around them into one hundred dollars in column “I”.

  • At first, we should calculate the amount of salary in the new year.

excel table

Now we want to round them in Penny, Dollar and one hundred dollars.

How to Round Number to Penny.

For rounding numbers to Penny, we should have 2 decimal places in our content.

For that we should use this command;

=ROUND(D4,2)

In this formula, “D4” is the address of the cell which we want to round its content and number 2 means that we want to round our numbers to the nearest two decimal places to the right of the decimals.

In this example, you can see in column “D”, If the numbers which are placed in the 3 decimal places are lower than 5 they will be rouned toward lower digit ( the rows number 4&5&7) but If the numbers are equal or higher than 5 they will be rounded toward higher digit.

excel sallary decimal

How to Round Number to Dollar.

For rounding numbers to the dollar, we should exclude decimal places in our content.

For that we should use this command;

=ROUND(D4,0)

In this formula, “D4” is the address of the cell which we want to round its content and number 0 means that we want to remove all decimals from our contents.

Also, you can see in the column “D” when the first decimal was equal or higher than five, our numbers were rounded to the higher digit in this example you can see rows number five and six and seven, when it was lower than five, it was rounded to lower digit like the column four.

excel sallary dollar

How to Round Number to Hundred Dollars.

For rounding salaries to the nearest hundred dollars. we should round our numbers to the nearest two digit places to the left side of the decimal point. for that, we should place minus 2 instead of zero in the previous formula.

=ROUND(D4,-2)

excel sallary

How to Round Up in Excel;

If you want to round your digits up, you can use from other commands.

Here I am going to introduce two commands for rounding up the digits in excel.

How to Round Up in Excel with CEILING.MATH Command;

This command used for roundup to the nearest integer or to the nearest multiple of significance and it has 3 different part which two of them are optional.

  • The first part is “Number” and that is the value which you want to round.

  • The second part is “Significance” and it is optional and is the multiple to which you want to round numbers.

  • The third part is “Mode” and It does not work for positive numbers and shows that number should round away or toward from zero.

In the below picture we use this command with “Mode” and without “Mode”.

As we said you can use this part only for negative numbers.

In the first and second rows, we wrote number 1 in the third part of the formula and you can see these numbers rounded away from zero,

and in the other rows, we didn’t write anything in the third part of the formula and you can see the numbers rounded toward zero.

mode

Here we are going to explain them with some example.

How to Round Up Numbers to 0.5

As you see in the first-row number remained the same because it is the multiple of 0.5 and rows number two and three rounded up to 315.5 and the last row rounded up to 316.

ceiling math 0.5

How to Round Up Numbers to 1;

in this table you can see the value in the first table did not change but in other rows, the value rounded up to the next digit.

ceiling math 1

How to Round Up Numbers to 10;

In this table, you can see the numbers rounded up to the next multiple 10 digits.

ceiling math 10

How to Round Up Numbers to 100;

In this table, you can see the numbers rounded up to the next multiple 100 digits.

ceiling math 100

How to Round Up in Excel with Round Up Command;

This command has two-part,

  • The first part is the number which you want to round it.

  • The second part is the number of digits to which you want to round number.

If you enter a negative number in this part the numbers will be rounded to the left side of the decimal point and the number zero will be rounded up the numbers to the nearest integer and positive number will be rounded up numbers to the specified number of the decimal.

Now we will explain this command with some example;

In this picture, you can see negative numbers in the first column and you can see these numbers round away zero.

roundupzero

In this formula, we entered number -2 in the second part and you can see the numbers in the table were rounded from the two left-side digits of the decimal point.

In this table, the first row did not round up because it was equal to multiple hundred.

roundup 02

In this formula, we entered number -1 in the second part and you can see the numbers in the table were rounded from the one left side digit of the decimal point.

In the first and third rows because they were integer the program did not round them up.

roundup 01

In this formula, we entered number zero in the second part and you can see the numbers in the table were rounded up to the nearest integer.

But in the first row because the number was integer the program did not round it up.

roundup0

In this formula, we entered number +1 in the second part so the numbers will be rounded up to the first decimal number.

roundup1

How to Round Up digits to 0.99

Sometimes you want to round up the digits to 0.99 for instance round 6.3 to 6.99

For that you can use this formula;

=ROUNDUP(E5,0)-0.01

In this formula, “E5” is the address of your desired cell.

round to 99

excel round