Wednesday, December 09, 2009

List of 50 common excel worksheet functions

This is the list of 50 common excel functions that I use on regular basis for all my work related task. I think anyone doing work on excel on regular basis should be conversant with all these formulas.

See how many of this do you know?

abs

Returns the absolute value of a number. The absolute value of a number is the number without its sign.

acos

Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi.

asin

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine isnumber. The returned angle is given in radians in the range -pi/2 to pi/2.

atan

Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2.

average

Returns the average (arithmetic mean) of the arguments.

choose

Uses index_num to return a value from the list of value arguments.  Ex. CHOOSE(index_num,value1,value2,...)

column

Returns the column number of the given reference.

columns

Returns the number of columns in an array or reference

concatenate

Joins several text strings into one text string. CONCATENATE (text1,text2,...)

convert

Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers. CONVERT(number,from_unit,to_unit)

cos

Returns the cosine of the given angle.

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

countblank

Counts empty cells in a specified range of cells.

countif

Counts the number of cells within a range that meet the given criteria

Date

Returns the sequential serial number that represents a particular date DATE(year,month,day)

day

Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.

degrees

Converts radians into degrees

find

FIND finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.  FIND(find_text,within_text,start_num)

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

hyperlink

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet.

if

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE

index

Returns a value or the reference to a value from within a table or range.

indirect

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself

left

LEFT returns the first character or characters in a text string, based on the number of characters you specify

lookup

Returns a value either from a one-row or one-column range or from an array.

match

Returns the relative position of an item in an array that matches a specified value in a specified order.

max

Returns the largest value in a set of values.

min

Returns the smallest number in a set of values.

month

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

now

Returns the serial number of the current date and time.

offset

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

pi

Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.

power

Returns the result of a number raised to a power. POWER(number,power)

rand

Returns an evenly distributed random number greater than or equal to 0 and less than 1.

right

RIGHT returns the last character or characters in a text string, based on the number of characters you specify

row

Returns the row number of a reference.

rows

Returns the number of rows in a reference or array.

sin

Returns the sine of the given angle

sqrt

Returns a positive square root.

stdev

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

substitute

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; SUBSTITUTE(text,old_text,new_text,instance_num)

sum

Adds all the numbers in a range of cells.

sumif

Adds the cells specified by a given criteria.

tan

Returns the tangent of the given angle.

text

Converts a value to text in a specific number format. TEXT(value,format_text)

transpose

Returns a vertical range of cells as a horizontal range, or vice versa.

trim

Removes all spaces from text except for single spaces between words.

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.

weekday

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

year

Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999

 

That’s it. Read more excel related post by clicking on excel

Subscribe to my-aesi via mail or rss by clicking subscribe

Search This Blog