Excel FORMULA Function - Yodalearning

[Pages:40]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

Functions

Syntax

Description

PROPER

=PROPER(text)

Capitalizes the first letter in each word of a text value

REPLACE

=REPLACE(old_text,start_nu m,num_chars,new_text) Replaces characters within text

RIGHT

Returns the rightmost characters

=RIGHT(text,num_chars)

from a text value

SEARCH

=SEARCH(find_text,within_te Finds one text value within

xt,start_num)

another (not case-sensitive)

TEXT

Formats a number and converts it

=TEXT(value,format_text)

to text

TRIM

=TRIM(text)

Removes spaces from text

UPPER

=UPPER(text)

Converts text to uppercase

DGET

Extracts from a database a single

record that matches the specified

=DGET(database,field,criteria)

criteria

DSUM

Adds the numbers in the field

=DSUM(database,field,criteria column of records in the database

)

that match the criteria

DAYS360

Calculates the number of days

=DAYS360(start_date,end_da between two dates based on a 360-

te,method)

day year

EDATE

Returns the serial number of the =EDATE(start_date,months) date that is the indicated number

Functions

Syntax

Description

of months before or after the start date

EOMONTH

Returns the serial number of the

=EOMONTH(start_date,mont last day of the month before or

hs)

after a specified number of months

=NETWORKDAYS(start_date, Returns the number of whole

NETWORKDAYS

end_date,[holidays])

workdays between two dates

Returns the number of whole

=NETWORKDAYS.INTL(start workdays between two dates using

NETWORKDAYS.IN _date,end_date,[weekend],[ho parameters to indicate which and

TL

lidays])

how many days are weekend days

WEEKDAY

=WEEKDAY(serial_number,[r Converts a serial number to a day

eturn_type])

of the week

WEEKNUM

Converts a serial number to a

=WEEKNUM(serial_number,[ number representing where the

return_type])

week falls numerically with a year

WORKDAY

Returns the serial number of the

=WORKDAY(start_date, days, date before or after a specified

[holidays])

number of workdays

WORKDAY.INTL

Returns the serial number of the date before or after a specified

number of workdays using =WORKDAY.INTL(start_date, parameters to indicate which and

days,weekend,holidays) how many days are weekend days

YEARFRAC

Returns the year fraction

representing the number of whole

=YEARFRAC(start_date,end_ days between start_date and

date,basis)

end_date

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download