Excel Rounding Numbers

We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more. To know how to use all these formulas and how to round numbers based on any criteria, just read on.

Rounding Formulas in Excel

Before learning the tips, first lets understand various rounding formulas & what they do. Look at this:

Formula	What it does?
ROUND	Rounds a number to specified decimal points (or multiples of 10)
ROUNDUP	Rounds up a number
ROUNDDOWN	Rounds down a number
MROUND	Rounds to nearest multiple of specified number
INT	Rounds down to nearest integer
TRUNC	Gives you only integer portion
CEILING	Rounds up a number to nearest multiple of 1,10,100…
FLOOR	Rounds down a number to nearest multiple of 1,10,100…
EVEN	Gives next even number
ODD	Gives next odd number
FIXED	Rounds and converts to text format (with commas if you want)

Rounding Formula Tips

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)-1 To 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

The INDEX Function
• =INDEX(array,row_number,column_Number)
• =INDEX(D3:M9,6,3) returns content of the cell at the intersection of the sixth row and
third column of the range D3:M9.
Note: INDEX by itself is useful, but when combined with MATCH becomes very flexible.
INDEX and MATCH Together
• =INDEX(array,MATCH(lookup_value,lookup_array,[match_type]),[column_num])
• =INDEX(D2:M9,Match(A2,C2:C9,0),3)
• =INDEX(array, row_num,MATCH(lookupValue,Lookup_array, [matchType]))
• =INDEX(D2:M9,7,Match(A2,C2:C9,0))
Example: In the screen shot below, a value is interred in cell B15. The value in cell B15 is compared to a range
of information in cells A4:A12. If a MATCH is found, a row number is given. To find the column number, a
value is added to cell D15. This information is compared to the values shown in cells E3:K3. When an exact
match is found, a column number is returned. Having both a row and column number, a weekly test score is
displayed from the table D4:K12.
 =INDEX(D4:K12,MATCH(D15,A4:A12,0),MATCH(B15,D3:K3,0))
7
PARTICIPANT NOTEBOOK

WTEXF030116