What causes the following errors to occur?
1
###
2 #NAME?
3
#REF!
4
#DIV/0!
5
#VALUE!
too many characters to display in column width
no named range or cell
cell (or range) reference no longer exists
dividing by zero
using text in an expression
What will Excel display if you enter 11/06 into a cell?
eg. 11/06
to see the result, remove the ' at the beginning of the cell entry
Function
Form
Name
Name( Arguments)
NOW
=NOW()
TODAY
=TODAY()
YEAR
=YEAR(serial_number)
DAY
MONTH
DATE
Example
5/18/2016 22:11
5/18/2016
2016
Description / Use
ALWAYS begin a formula with an equal sign = followed by the name of
function then parentheses (). Inside the parentheses go cell or cell
ranges,operators, or logical tests.
Returns the current date and time formatted as a date and time.
Returns the current date formatted as a date.
Returns the year of a date, an integer between 1900 and 9999.
=DAY(serial_number)
18
Returns the day of the month, a number from 1 to 31.
=MONTH(serial_number)
Returns the month of the year, a number from 1 to 12.
=DATE(year,month,day)
5/18/2016
Keyboard shortcut to insert
today's date as a static entry Crtl + ;
into a worksheet:
3/27/2006
Keyboard shortcut to insert
current time as a static entry Crtl + Shift + ;
into a worksheet:
12:17 PM
Returns a date formatted as a date
Function
Form
Example
Description / Use
1
2
Name
ALWAYS begin a formula with an equal sign = followed by
the name of function then parentheses (). Inside the
parentheses go cell or cell ranges,operators, or logical
tests.
Name( Arguments)
4
5
COUNTIF COUNTIF(CellRange,Logical Test)
=COUNTIF(C16:D19,">5")
=COUNTIF(C16:D19,">"&D6)
Counts the cells where the logical test is true from within a
range of cells.
IF Logical Test is ">5" then it counts all cells with a cell
value greater than five.
8
9
10
11
IF
=IF(C17>D17, "Pass", "Fail")
12
VLOOKUP VLOOKUP(lookup_value, table, column_num)
13
=VLOOKUP(C17,$D$16:$E$19, 2)
14
15
16
17
18
19
Returns one of two values depending on whether or not the
test returns true or false.
IF(Logical Test, true value, false value)
4
7
7
8
Pass
0
5
10
15
Looks up the value for cell C15 in the table D16:E19 finding
an equivalent value from column 2.
The Match value has been omitted which means the
function will look for a close rather than an exact match.
A
B
C
D
1
2
1
2
Function
Form
Example
Description / Use
ALWAYS begin a formula with an equal sign = followed by the name of
function then parentheses (). Inside the parentheses go cell or cell
ranges,operators, or logical tests.
Name
Name( Arguments)
SUM
SUM(number1, number2, ...)
3
4
5
5
6
7
7
8
9
10
11
12
13
14
15
8
9
10
Label
12
13
14
=SUM(A1:A11)
AVERAGE
MAX
MIN
55
Returns the sum of all the values in the cell range A1 through A11. *
=AVERAGE(A1:A11)
5.5
Returns the average for all the values in the cell range A1 through A11.
*
MAX(number1, number2, )
=MAX(A1:A15)
14
Returns the greatest value from within a range of cells. *
MIN(number1, number2, )
=MIN(A1:A15)
Returns the least value from within a range of cells. *
13
Counts the number of cells with values (numbers) in the range A1
through A15. *
=COUNTA(A1:A15)
14
Counts the number of cells with values and labels in the range A1
through A15. *
INT(number)
=INT(D24)
3.64159
3
Rounds the number down to the nearest integer.
ROUND(number,num_digits)
=ROUND(D27,3)
3.14159
3.142
Rounds a number to a specified number of digits.
Rand()
Returns an evenly distributed random number greater than or equal to
0.9049069814 0 and less than 1. A new random number is returned every time the
worksheet is calculated.
AVERAGE(number1, number2, )
16
17
COUNT
COUNT(number1, number2, )
=COUNT(A1:A15)
18
19
20
COUNTA
21
COUNTA(number1, number2, )
22
23
24
INT
25
26
27
ROUND
28
29
30
RAND
31
32
* Can include ranges and cells separated by commas.
A
1
2
Student ID
6912501
First and Middle
John David
Last
Roberts
Driver Master Number
ROBER240474003
3
4
Function
Form
Name
Name( Arguments)
LEFT
LEFT(Cell, Number of characters)
=LEFT(C2, 1)
Example
Description / Use
ALWAYS begin a formula with an equal sign = followed by the name of
function then parentheses (). Inside the parentheses go cell or cell
ranges,operators, or logical tests.
Returns a specified number of characters starting at the leftmost
character in the text string value of cell C2.
8
9
RIGHT
=RIGHT(B2,2)
10
Returns a specified number of characters starting at the rightmost
character in the text string value of cell B2.
RIGHT(Cell, Number of characters)
01
11
12
LEN
13
=LEN(Cell)
=LEN(C2)
Returns the length of the text in C2. Spaces count as characters.
10
14
15
MID
MID(Cell, start_num, num_chars)
=MID(E2,6,6)
16
Returns the number of characters you have specified from E2,
starting at the position you specified. The first character has
start_num 1.
240474
17
18
FIND
FIND(find_text, within text, [start_num])
=FIND("D",C2)
19
Returns the starting position of one text string within another
text string. FIND is case-sensitive. Wildcards are not allowed. If
the text is not found, an error is returned. Start-num is 1 if
omitted.
6
20
21
CONCATENATE
=CONCATENATE(first_cell, second_cell)
22
=CONCATENATE(D7," ",D2)
J Roberts
23
=C2&" "&D2
John David Roberts
24
Joins (concatenates) the values of two or more cells.
If D7 = J and D2 = Roberts then the function would return "J Roberts"
as a concatenated text string.
This form of concatenation would return "John Daivd Roberts" with a
space between the first name and last name.
A
25
UPPER
26
=UPPER(Cell)
=UPPER(C2)
Converts the text string in C2 to all uppercase letters.
JOHN DAVID
27
28
LOWER
29
=LOWER(Cell)
=LOWER(C2)
Converts the text string in C2 to all lower case letters.
john david
30
31
TRIM
32
33
34
35
PROPER
=TRIM(Cell)
=TRIM(D31)
John David
John David
=PROPER(cell)
john dAVID
=PROPER(D34)
John David
Removes all spaces in a text string except for a single space between
words.
Capitalizes the first letter in a text string and any other letters in text that
follow any character other than a letter. Converts all other letters to
lowercase letters.
