DAVERAGE

Averages the values in a column of a list or database that match conditions you specify.
Syntax
DAVERAGE(database,field,criteria)
Database    is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field    indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria    is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

Example
The example may be easier to understand if you copy it to a blank worksheet.

	A	B	C	D	E
1	Tree	Height	Age	Yield	Profit
2	Apple	>10
3	Pear
4	Tree	Height	Age	Yield	Profit
5	Apple	18	20	14	105.00
6	Pear	12	12	10	96.00
7	Cherry	13	14	9	105.00

COUNTBLANK

Counts empty cells in a specified range of cells.
Syntax
COUNTBLANK(range)
Range    is the range from which you want to count the blank cells.
Remark
Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.
Example
The example may be easier to understand if you copy it to a blank worksheet.

	A	B
1	Data	Data
2
3	6	=IF(B4<30,"",B4)
4		27
5	4	34

Formula	Description (Result)
=COUNTBLANK(A2:B5)	Counts empty cells in the range above. The formula returns empty text. (4)

HOUR

Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
Syntax
HOUR(serial_number)
Serial_number    is the time that contains the hour you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).
Remark
Microsoft Excel for Windows and Excel for the Macintosh use different date systems as their defaults. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).
Example
The example may be easier to understand if you copy it to a blank worksheet.

	A
1	Time
2	3:30:30 AM
3	3:30:30 PM
4	15:30

Formula	Description (Result)
=HOUR(A2)	Hour of first time (3)
=HOUR(A3)	Hour of second time (15)
=HOUR(A4)	Hour of third time (15)

COS

Returns the cosine of the given angle.
Syntax
COS(number)
Number    is the angle in radians for which you want the cosine.
Remark
If the angle is in degrees, multiply it by PI()/180 or use the COS function to convert it to radians.
Example
The example may be easier to understand if you copy it to a blank worksheet.

	A	B
1	Formula	Description (Result)
2	=COS(1.047)	Cosine of 1.047 radians (0.500171)
3	=COS(60*PI()/180)	Cosine of 60 degrees (0.5)
4	=COS(RADIANS(60))	Cosine of 60 degrees (0.5)

CONCATENATE

Joins several text strings into one text string.
Syntax
CONCATENATE (text1,text2,...)
Text1, text2, ...    are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.
Remarks
The "&" operator can be used instead of CONCATENATE to join text items.
Example
The example may be easier to understand if you copy it to a blank worksheet.

	A
1	Data
2	brook trout
3	species
4	32

 

Formula	Description
=CONCATENATE("Stream population for ",A2," ",A3," is ",A4,"/mile")	Concatenates a sentence from the data above (Stream population for brook trout species is 32/mile)

VLOOKUP

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The V in VLOOKUP stands for "Vertical."
Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value    is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.
Table_array    is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

·         If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

·         You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

·         The values in the first column of table_array can be text, numbers, or logical values.

·         Uppercase and lowercase text are equivalent.

Col_index_num    is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
Range_lookup    is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
Remarks

• If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
• If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Example

HLOOKUP

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The H in HLOOKUP stands for "Horizontal."
Syntax
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value    is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.
Table_array    is a table of information in which data is looked up. Use a reference to a range or a range name.

·         The values in the first row of table_array can be text, numbers, or logical values.

·         If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

·         Uppercase and lowercase text are equivalent.

·         You can put values in ascending order, left to right, by selecting the values and then clicking Sort on the Data menu. Click Options, click Sort left to right, and then click OK. Under Sort by, click the row in the list, and then click Ascending.

Row_index_num    is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.
Range_lookup    is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

AND

Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
Syntax
AND(logical1,logical2, ...)
Logical1, logical2, ...    are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
Remarks

• The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values.
• If an array or reference argument contains text or empty cells, those values are ignored.
• If the specified range contains no logical values, AND returns the #VALUE! error value.

Example 1
The example may be easier to understand if you copy it to a blank worksheet.

	A	B
1	Formula	Description (Result)
2	=AND(TRUE, TRUE)	All arguments are TRUE (TRUE)
3	=AND(TRUE, FALSE)	One argument is FALSE (FALSE)
4	=AND(2+2=4, 2+3=5)	All arguments evaluate to TRUE (TRUE)

DAY

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
Syntax
DAY(serial_number)
Serial_number    is the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
Remarks
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.
Example
The example may be easier to understand if you copy it to a blank worksheet.

	A
1	Date
2	15-Apr-2008

Formula	Description (Result)
=DAY(A2)	Day of the date above (15)

DATEVALUE

Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.
Syntax
DATEVALUE(date_text)
Date_text    is text that represents a date in a Microsoft Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates. Using the default date system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Using the default date system in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.
If the year portion of date_text is omitted, DATEVALUE uses the current year from your computer's built-in clock. Time information in date_text is ignored.
Remarks

• Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for the Macintosh uses a different date system as its default.
• Most functions automatically convert date values to serial numbers.
  

DATE

Returns the sequential serial number that represents a particular date. If the cell format was General before the function was entered, the result is formatted as a date.
Syntax
DATE(year,month,day)
Year    The year argument can be one to four digits. Microsoft Excel interprets the year argument according to the date system you are using. By default, Excel for Windows uses the 1900 date system; Excel for the Macintosh uses the 1904 date system.

For the 1900 date system

·         If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).

·         If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2008,1,2) returns January 2, 2008.

·         If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

For the 1904 date system

·         If year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).

·         If year is between 1904 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2008,1,2) returns January 2, 2008.

·         If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive), Excel returns the #NUM! error value.

Month    is a number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,14,2) returns the serial number representing February 2, 2009.
Day    is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008,1,35) returns the serial number representing February 4, 2008.

MIN

Returns the smallest number in a set of values.
Syntax
MIN(number1,number2,...)
Number1, number2, ...    are 1 to 30 numbers for which you want to find the minimum value.
Remarks

• You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.
• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text should not be ignored, use MINA instead.
• If the arguments contain no numbers, MIN returns 0.

Example

SUMIF

Adds the cells specified by a given criteria.
Syntax
SUMIF(range,criteria,sum_range)
Range    is the range of cells you want evaluated.
Criteria    is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
Sum_range    are the actual cells to sum.
Remarks

• The cells in sum_range are summed only if their corresponding cells in range match the criteria.
  
• If sum_range is omitted, the cells in range are summed.
  
• Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.
  

Example
The example may be easier to understand if you copy it to a blank worksheet.

	A	B
1	Property Value	Commission
2	100,000	7,000
3	200,000	14,000
4	300,000	21,000
5	400,000	28,000

Formula	Description (Result)
=SUMIF(A2:A5,">160000",B2:B5)	Sum of the commissions for property values over 160000 (63,000)

SIN

Returns the sine of the given angle.
Syntax
SIN(number)
Number    is the angle in radians for which you want the sine.
Remark
If your argument is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians.
Example
The example may be easier to understand if you copy it to a blank worksheet.

	A	B
1	Formula	Description (Result)
2	=SIN(PI())	Sine of pi radians (0, approximately)
3	=SIN(PI()/2)	Sine of pi/2 radians (1)
4	=SIN(30*PI()/180)	Sine of 30 degrees (0.5)
5	=SIN(RADIANS(30))	Sine of 30 degrees (0.5)

MAX

Returns the largest value in a set of values.
Syntax
MAX(number1,number2,...)
Number1, number2, ...    are 1 to 30 numbers for which you want to find the maximum value.
Remarks

• You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.
• If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text must not be ignored, use MAXA instead.
• If the arguments contain no numbers, MAX returns 0 (zero).

Example
The example may be easier to understand if you copy it to a blank worksheet.

	A
1	Data
2	10
3	7
4	9
5	27
6	2

Formula	Description (Result)
=MAX(A2:A6)	Largest of the numbers above (27)
=MAX(A2:A6, 30)	Largest of the numbers above and 30 (30)

COUNT

Counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that's in a range or array of numbers.
Syntax
COUNT(value1,value2,...)
Value1, value2, ...    are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.
Remarks

• Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. If you need to count logical values, text, or error values, use the COUNTA function.

Example

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet. When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file stored at link_location.

Syntax
HYPERLINK(link_location,friendly_name)

Link_location is the path and file name to the document to be opened as text. Link_location can refer to a place in a document— such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file stored on a hard disk drive, or the path can be a universal naming convention (UNC) path on a server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL) path on the Internet or an intranet.

· Link_location can be a text string enclosed in quotation marks or a cell that contains the link as a text string.

· If the jump specified in link_location does not exist or cannot be navigated, an error appears when you click the cell.

Friendly_name is the jump text or numeric value that is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.

· Friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value.

· If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.

Remark
To select a cell that has a hyperlink in it without jumping to the hyperlink destination, click the cell and hold the mouse button until the cursor becomes a cross (+), then release the mouse button.
Examples

IF

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
Syntax
IF(logical_test,value_if_true,value_if_false)
Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.
Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.
Remarks

• Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last of the following examples.
• When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
• If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out.
• Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF worksheet function. To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. Learn about calculating a value based on a condition.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

1 2

A Data 50

Formula Description (Result) =IF(A2<=100,"Within budget","Over budget") If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget) =IF(A2=100,SUM(B5:B15),"") If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()

Example 2
The example may be easier to understand if you copy it to a blank worksheet.

1 2 3 4

A B Actual Expenses Predicted Expenses 1500 900 500 900 500 925

Formula Description (Result) =IF(A2>B2,"Over Budget","OK") Checks whether the first row is over budget (Over Budget) =IF(A3>B3,"Over Budget","OK") Checks whether the second row is over budget (OK)

Example 3

AVERAGE

AVERAGE

Returns the average (arithmetic mean) of the arguments.
Syntax
AVERAGE(number1,number2,...)
Number1, number2, ...    are 1 to 30 numeric arguments for which you want the average.
Remarks

• The arguments must either be numbers or be names, arrays, or references that contain numbers.
• If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Tip

When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Zero values check box on the View tab (Options command, Tools menu). Empty cells are not counted, but zero values are.

Example
The example may be easier to understand if you copy it to a blank worksheet.

1 2 3 4 5 6

A Data 10 7 9 27 2

Formula	Description (Result)
=AVERAGE(A2:A6)	Average of the numbers above (11)
=AVERAGE(A2:A6, 5)	Average of the numbers above and 5 (10)

SUM

SUM

Adds all the numbers in a range of cells.
Syntax
SUM(number1,number2, ...)
Number1, number2, ...    are 1 to 30 arguments for which you want the total value or sum.
Remarks

• Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.
• If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.
• Arguments that are error values or text that cannot be translated into numbers cause errors.

Example

The example may be easier to understand if you copy it to a blank worksheet.

1 2 3 4 5 6

A Data -5 15 30 '5 TRUE

Formula	Description (Result)
=SUM(3, 2)	Adds 3 and 2 (5)
=SUM("5", 15, TRUE)	Adds 5, 15 and 1, because the text values are translated into numbers, and the logical value TRUE is translated into the number 1 (21)
=SUM(A2:A4)	Adds the first three numbers in the column above (40)
=SUM(A2:A4, 15)	Adds the first three numbers in the column above, and 15 (55)
=SUM(A5,A6, 2)	Adds the values in the last two rows above, and 2. Because nonnumeric values in references are not translated, the values in the column above are ignored (2)