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
