Scribd
Upload
101 views3 pages

Excel Rounding Formulas Guide

This document provides examples and explanations for 18 different rounding and number formatting formulas in Excel. These include formulas to round numbers to various decimal places or the nearest integer, multiple, thousand, etc. Formulas are also provided to round numbers up or down, extract the integer or decimal portion, and convert numbers to text with a specified number of decimals. Additional examples show how to use rounding to get the next even, odd, or quarter number. The document concludes by suggesting two additional formulas for tip-friendly pricing and rounding dates to the nearest Monday.

Uploaded by

Gmd Nizam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
101 views3 pages

Excel Rounding Formulas Guide

This document provides examples and explanations for 18 different rounding and number formatting formulas in Excel. These include formulas to round numbers to various decimal places or the nearest integer, multiple, thousand, etc. Formulas are also provided to round numbers up or down, extract the integer or decimal portion, and convert numbers to text with a specified number of decimals. Additional examples show how to use rounding to get the next even, odd, or quarter number. The document concludes by suggesting two additional formulas for tip-friendly pricing and rounding dates to the nearest Monday.

Uploaded by

Gmd Nizam
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

1.

Round to 2 decimal points


Example: 1.2649 to 1.26
=ROUND(A1,2)Rounds value in A1 by 2 decimal points

2. Round up to 2 decimal points


Example: 1.2649 to 1.27
=ROUNDUP(A1,2)Roundsup value in A1 by 2 decimal points (ie away from zero)

3. Round to nearest integer


Example: 1.2649 to 1
=ROUND(A1,0)By using 0, we can round the value to nearest integer

4. Round to nearest multiple of 10


Example: 544.234 to 540
=ROUND(A1,-1)By using negative numbers, we can round the value to nearest multiple of 10, 100

5. Round up to nearest multiple of 10


Example: 544.234 to 550

=ROUNDUP(A1,-1)

6. Round to nearest thousand


Example: 312789123 to 312789000

=ROUND(A1,-3)

7. Round to nearest million with one decimal point


Example: 312789123 to 312.8
=ROUND(A1/1000000,1)First we divide the number by million (1,000,000) and then round this to 1
decimal point.

8. Round to nearest multiple of 2


Example: 43 to 44
=MROUND(A1,2)Just like round formula, but for any multiple. So MROUND(A1,2) takes value in A1
and rounds it to nearest multiple of 2

9. Round to nearest multiple of 5


Example: 93 to 95

=MROUND(A1,5)

10. Round down to hundred


Example: 301 to 300
=FLOOR(A1,100)To round down, we can use FLOOR formula.

11. Get only the integer portion of a number


Example: -23.34 to -23
=TRUNC(A1,0)To extract only the integer portion of number, use TRUNC formula. Note: INT formula
gives same result for positive numbers.

12. Round a number to 2 decimals and convert to text


Example: 312789.26921 to 312,789.27
=FIXED(A1,2,FALSE)In one shot, round and convert the number to text. Useful when you want text
output.

13. Get next even number


Example: 42.1 to 44
=EVEN(A1)Gets you next EVEN number (away from zero)

14. Get next odd number


Example: 44.93 to 45

=ODD(A1)

15. Round to nearest quarter ($0.25)


Example: 19.14 to 19.25
=MROUND(A1,0.25)MROUND can be used with fractions too.

16. Round to next 9 (ie 19,29,39 etc.)


Example: 23 to 29

=ROUNDUP(A1,-1)-1To do this, we just roundup the number to next 10 and then subtract 1 from it.

17. Round up to next 1000


Example: 124567 to 125000
=CEILING(A1,1000)Just like FLOOR, but takes you to next value.

18. Get only decimal portion of a number


Example: 23.345 to 0.345
=A1-TRUNC(A1)To get only decimal portion, subtract TRUNC value from original

Download Rounding formula example workbook


Click here to download example workbook & understand these formulas better.

What about .2 tips?


Well, those are for you to fill down. Go ahead and write formulas for both these situations & you have
the .2 tips!

18.1 Waiter friendly pricing


Lets say you run a hotel where customers usually tip 15% of bill amount. Now, to make it easy, you
want to price your items such that when 15% is added, the total amount becomes a round number
like $1.00, $2.00 etc.
For example: If a dishs current price is $2.50, then 15% tip on it would be $0.37. This makes the
total $2.87.
If you modify the price to $2.60, with tip the total would be $3.00.
Assuming current price of a dish is in A1, what formula will give you new price?

18.2 Rounding to nearest Monday


Lets say you have some dates in a list and you want them to round to nearest Monday. Assuming
you have a date in A1, what formula gives nearest Monday?

You might also like