Functions Syntax Description DATE
Functions
Syntax
Description
DATE
=DATE(year,month,day)
Returns the serial number of a particular date
DATEVALUE
Converts a date in the form of text
=DATEVALUE(date_text)
to a serial number
DAY
=DAY(serial_number)
Converts a serial number to a day of the month
HOUR
=HOUR(serial_number)
Converts a serial number to an hour
MINUTE
=MINUTE(serial_number)
Converts a serial number to a minute
MONTH
=MONTH(serial_number)
Converts a serial number to a month
NOW
=NOW()
Returns the serial number of the current date and time
SECOND
=SECOND(serial_number)
Converts a serial number to a second
TIME
Returns the serial number of a
=TIME(hour,minute,second)
particular time
TIMEVALUE
Converts a time in the form of text
=TIMEVALUE(time_text)
to a serial number
TODAY
=TODAY()
Returns the serial number of today's date
Functions
Syntax
Description
YEAR
=YEAR(serial_number) Converts a serial number to a year
CELL
Returns information about the
formatting, location, or contents of
=CELL(info_type, [reference])
a cell
ISBLANK
=ISBLANK(value)
Returns TRUE if the value is blank
ISERROR
=ISERROR(value)
Returns TRUE if the value is any error value
ISNONTEXT
=ISNONTEXT(value)
Returns TRUE if the value is not text
ISNUMBER
=ISNUMBER(value)
Returns TRUE if the value is a number
ISTEXT
=ISTEXT(value)
Returns TRUE if the value is text
AND
=AND(logical1,logical2,...)
Returns TRUE if all of its arguments are TRUE
FALSE
=FALSE
Returns the logical value FALSE
=IF(logical_test,
[value_if_true],
IF
[value_if_false])
Specifies a logical test to perform
IFERROR
=IFERROR(value, value_if_error)
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the
formula
Functions
Syntax
Description
NOT
=NOT(logical)
Reverses the logic of its argument
Returns TRUE if any argument is
OR
=OR(logical1,logical2,...)
TRUE
TRUE
=TRUE
Returns the logical value TRUE
ADDRESS
=ADDRESS(row_num, column_num, [abs_num],
[a1], [sheet_text])
Returns a reference as text to a single cell in a worksheet
COLUMN
=COLUMN([reference])
Returns the column number of a reference
COLUMNS
=COLUMNS(array)
Returns the number of columns in a reference
HLOOKUP
=HLOOKUP(lookup_value,tab Looks in the top row of an array
le_array,row_index_num,[ran and returns the value of the
ge_lookup])
indicated cell
INDEX
=INDEX(array,row_num,[colu Uses an index to choose a value
mn_num]) ? 2 types
from a reference or array
INDIRECT
Returns a reference indicated by a
=INDIRECT(ref_text,a1)
text value
LOOKUP
=LOOKUP(lookup_value, array) ? 2 types
Looks up values in a vector or array
MATCH
=MATCH(lookup_value,looku Looks up values in a reference or
p_array,match_type)
array
Functions
Syntax
Description
OFFSET
=OFFSET(reference,rows,cols, Returns a reference offset from a
height,width)
given reference
ROW
=ROW([reference])
Returns the row number of a reference
ROWS
=ROWS(array)
Returns the number of rows in a reference
VLOOKUP
=VLOOKUP(lookup_value,tab Looks in the first column of an
le_array,col_index_num,[rang array and moves across the row to
e_lookup])
return the value of a cell
Returns the absolute value of a
ABS
=ABS(number)
number
PRODUCT
=PRODUCT(number1,number 2,...)
Multiplies its arguments
RAND
=RAND()
Returns a random number between 0 and 1
=RANDBETWEEN(bottom,to Returns a random number
RANDBETWEEN
p)
between the numbers you specify
ROUND
=ROUND(number,num_digits Rounds a number to a specified
)
number of digits
=ROUNDDOWN(number,nu Rounds a number down, toward
ROUNDDOWN
m_digits)
zero
ROUNDUP
=ROUNDUP(number,num_di Rounds a number up, away from
gits)
zero
Functions
Syntax
Description
SUBTOTAL
=SUBTOTAL(function_num,r Returns a subtotal in a list or
ef1,...)
database
SUM
=SUM(number1,number2,...)
Adds its arguments
SUMIF
=SUMIF(range,criteria,[sum_ Adds the cells specified by a given
range])
criteria
SUMIFS
=SUMIFS(sum_range,criteria Adds the cells in a range that meet
_range,criteria,...)
multiple criteria
=SUMPRODUCT(array1,array Returns the sum of the products of
SUMPRODUCT
2,[array3],...)
corresponding array components
AVERAGE
=AVERAGE(number1,number 2,...)
Returns the average of its arguments
AVERAGEIF
Returns the average (arithmetic
=AVERAGEIF(range,criteria,[ mean) of all the cells in a range
average_range])
that meet a given criteria
COUNT
Counts how many numbers are in
=COUNT(value1,value2,...)
the list of arguments
COUNTA
Counts how many values are in the
=COUNTA(value1,value2,...)
list of arguments
COUNTBLANK
=COUNTBLANK(range)
Counts the number of blank cells within a range
COUNTIF
Counts the number of cells within =COUNTIF(range,criteria) a range that meet the given criteria
Functions
Syntax
Description
COUNTIFS
=COUNTIFS(criteria_range,cr Counts the number of cells within
iteria,...)
a range that meet multiple criteria
MAX
Returns the maximum value in a
=MAX(number1,number2,...)
list of arguments
MEDIAN
=MEDIAN(number1,number2, Returns the median of the given
...)
numbers
MIN
Returns the minimum value in a
=MIN(number1,number2,...)
list of arguments
Joins several text items into one
=CONCATENATE(text1,text2, text item. Easier to use `&' instead
CONCATENATE
...)
of the function usually.
EXACT
=EXACT(text1,text2)
Checks to see if two text values are identical
FIND
=FIND(find_text,within_text, start_num)
Finds one text value within another (case-sensitive)
LEFT
=LEFT(text,num_chars)
Returns the leftmost characters from a text value
LEN
=LEN(text)
Returns the number of characters in a text string
LOWER
=LOWER(text)
Converts text to lowercase
MID
Returns a specific number of
=MID(text,start_num,num_ch characters from a text string
ars)
starting at the position you specify
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- useful microsoft excel functions formulas webhome
- functions syntax description date
- nested ifs if and if or
- excel if
- using bloomberg to get the data you need
- mastering excel functions formulas
- 1 2 excel formulas
- advanced excel formulas functions
- introduction to eikon excel
- microsoft office excel 2007 higher technological institute