CASTILLO, GRASZIELLA A.

BSA – 2101
Functions Syntax Description
Returns the serial number of a particular
DATE
FORMULAS IN
=DATE(year,month,day)
date
Converts a date in the form of text to a
DATEVALUE =DATEVALUE(date_text)

DAY
MICROSOFT EXCEL
=DAY(serial_number)
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
Returns the serial number of the current
NOW =NOW()
date and time
SECOND =SECOND(serial_number) Converts a serial number to a second
Returns the serial number of a particular
TIME =TIME(hour,minute,second)
time
Converts a time in the form of text to a
TIMEVALUE =TIMEVALUE(time_text)
serial number
TODAY =TODAY() Returns the serial number of today’s date
YEAR =YEAR(serial_number) Converts a serial number to a year
Returns information about the formatting,
CELL =CELL(info_type, [reference])
location, or contents of a cell
ISBLANK =ISBLANK(value) Returns TRUE if the value is blank
Returns TRUE if the value is any error
ISERROR =ISERROR(value)
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
Returns TRUE if all of its arguments are
AND =AND(logical1,logical2,…)
TRUE
FALSE =FALSE Returns the logical value FALSE
=IF(logical_test, [value_if_true],
IF Specifies a logical test to perform
[value_if_false])
Returns a value you specify if a formula
IFERROR =IFERROR(value, value_if_error) evaluates to an error; otherwise, returns
the result of the formula
=OFFSET(reference,rows,cols,height,wi Returns a reference offset from a given
OFFSET
dth) reference
ROW =ROW([reference]) Returns the row number of a reference
ROWS =ROWS(array) Returns the number of rows in a reference
Looks in the first column of an array and
=VLOOKUP(lookup_value,table_array,
VLOOKUP moves across the row to return the value
col_index_num,[range_lookup])
of a cell
ABS =ABS(number) Returns the absolute value of a number
PRODUCT =PRODUCT(number1,number2,…) Multiplies its arguments
Returns a random number between 0 and
RAND =RAND()
1
Returns a random number between the
RANDBETWEEN =RANDBETWEEN(bottom,top)
numbers you specify
Rounds a number to a specified number of
ROUND =ROUND(number,num_digits)
digits
ROUNDDOWN =ROUNDDOWN(number,num_digits) Rounds a number down, toward zero
ROUNDUP =ROUNDUP(number,num_digits) Rounds a number up, away from zero
SUBTOTAL =SUBTOTAL(function_num,ref1,…) Returns a subtotal in a list or database
SUM =SUM(number1,number2,…) Adds its arguments
SUMIF =SUMIF(range,criteria,[sum_range]) Adds the cells specified by a given criteria
=SUMIFS(sum_range,criteria_range,crit Adds the cells in a range that meet
SUMIFS
eria,…) multiple criteria
=SUMPRODUCT(array1,array2, Returns the sum of the products of
SUMPRODUCT
[array3],…) corresponding array components
AVERAGE =AVERAGE(number1,number2,…) Returns the average of its arguments
Returns the average (arithmetic mean) of
=AVERAGEIF(range,criteria,
AVERAGEIF all the cells in a range that meet a given
[average_range])
criteria
Counts how many numbers are in the list
COUNT =COUNT(value1,value2,…)
of arguments
Counts how many values are in the list of
COUNTA =COUNTA(value1,value2,…)
arguments
Counts the number of blank cells within a
COUNTBLANK =COUNTBLANK(range)
range
Counts the number of cells within a range
COUNTIF =COUNTIF(range,criteria)
that meet the given criteria
Counts the number of cells within a range
COUNTIFS =COUNTIFS(criteria_range,criteria,…)
that meet multiple criteria
Returns the maximum value in a list of
MAX =MAX(number1,number2,…)
arguments
MEDIAN =MEDIAN(number1,number2,…) Returns the median of the given numbers
Returns the minimum value in a list of
MIN =MIN(number1,number2,…)
arguments
Joins several text items into one text item.
CONCATENATE =CONCATENATE(text1,text2,…) Easier to use ‘&’ instead of the function
usually.
Checks to see if two text values are
EXACT =EXACT(text1,text2)
identical
Finds one text value within another (case-
FIND =FIND(find_text,within_text,start_num)
sensitive)
Returns the leftmost characters from a
LEFT =LEFT(text,num_chars)
text value
Returns the number of characters in a text
LEN =LEN(text)
string
LOWER =LOWER(text) Converts text to lowercase
Returns a specific number of characters
MID =MID(text,start_num,num_chars) from a text string starting at the position
you specify
=REPLACE(old_text,start_num,num_ch
REPLACE Replaces characters within text
ars,new_text)
Returns the rightmost characters from a
RIGHT =RIGHT(text,num_chars)
text value
=SEARCH(find_text,within_text,start_n Finds one text value within another (not
SEARCH
um) case-sensitive)
TEXT =TEXT(value,format_text) Formats a number and converts it to text
TRIM =TRIM(text) Removes spaces from text
UPPER =UPPER(text) Converts text to uppercase
 Calculates the number of days between
DAYS360 =DAYS360(start_date,end_date,method)
two dates based on a 360-day year
N =N(value) Returns a value converted to a number
NA =NA() Returns the error value #N/A
Rounds a number up to the nearest even
EVEN =EVEN(number)
integer
Returns e raised to the power of a given
EXP =EXP(number)
number
FACT =FACT(number) Returns the factorial of a number
LCM =LCM(number1,number2,…) Returns the least common multiple
MOD =MOD(number,divisor) Returns the remainder from division
Returns a number rounded to the desired
MROUND =MROUND(number,multiple)
multiple
Rounds a number up to the nearest odd
ODD =ODD(number)
integer
PI =PI() Returns the value of pi
Returns the result of a number raised to a
POWER =POWER(number,power)
power
QUOTIENT =QUOTIENT(numerator,denominator) Returns the integer portion
SIGN =SIGN(number) Returns the sign of a number
SQRT =SQRT(number) Returns a positive square root
Returns the sum of the squares of the
SUMSQ =SUMSQ(number1,number2,…)
arguments
TRUNC =TRUNC(number,num_digits) Truncates a number to an integer
Returns the average of its arguments,
AVERAGEA =AVERAGEA(value1,value2,…)
including numbers,
Returns the most common value in a data
MODE =MODE(number1,number2,…)
set
Returns the k-th percentile of values in a
PERCENTILE =PERCENTILE(array,k)
range
Returns the percentage rank of a value in
PERCENTRANK =PERCENTRANK(array,x,significance)
a data set
POISSON =POISSON(x,mean,cumulative) Returns the Poisson distribution
QUARTILE =QUARTILE(array,quart) Returns the quartile of a data set
RANK =RANK(number,ref,order) Returns the rank of a number
Returns the average of selected database
DAVERAGE =DAVERAGE(database,field,criteria)
entries
Counts the cells that contain numbers in a
DCOUNT =DCOUNT(database,field,criteria)
database
DCOUNTA =DCOUNTA(database,field,criteria) Counts nonblank cells in a database
Returns the maximum value from selected
DMAX =DMAX(database,field,criteria)
database entries
Returns the minimum value from selected
DMIN =DMIN(database,field,criteria)
database entries
Multiplies the values in a particular field
DPRODUCT =DPRODUCT(database,field,criteria) of records that match the criteria in a
database
Estimates the standard deviation based on
DSTDEV =DSTDEV(database,field,criteria)
a sample of selected database entries
Calculates the standard deviation based on
DSTDEVP =DSTDEVP(database,field,criteria) the entire population of selected database
entries
 Estimates variance based on a sample
DVAR =DVAR(database,field,criteria)
from selected database entries
Calculates variance based on the entire
DVARP =DVARP(database,field,criteria)
population of selected