Excel Functions

The functions in Excel do most of the current work and there are functions to do many things.

The function can have parameters and you have to respect the rules of it.

Help in functions

If you press the equal sign it will show you a helping page explaining each of the parameters of the function and allowing you to ask for extra help.

Tip about Excel functions

You may create your own functions in VBA and use them where you want.

Excel Functions

Excel has hundreds of functions.

Mathematical Functions

The functions more used in some cases have more than one way to be done.

SUM, COUNT, MAX, MIN

Information functions

ISBLANK(): Checks whether a cell is empty
ISNUMBER(), ISTEXT(): Checks whether a cell contains a number or text

Statistics functions

COUNT(), COUNTA(): Counts the number of cells that contain numbers, and counts non-empty cells, respectively
MEDIAN(): Finds the median of a range
STDEV(): Estimates standard deviation based on a sample

Financial functions

PMT(): Calculates the payment for a loan based on constant payments and a constant interest rate
FV(): Calculates the future value of an investment
NPV(): Calculates the net present value of an investment

Logical functions

IF(): Performs a logical test and returns one value for a TRUE result, and another for a FALSE result
AND(), OR(): Checks whether all conditions in a test are TRUE, or if any of the conditions are TRUE, respectively
NOT(): Reverses the logic of its argument

Text functions

CONCATENATE(), TEXTJOIN(): Joins two or more text strings into one string
LEFT(), RIGHT(), MID(): Extracts a substring from a string starting from the left, right, or a specified position
UPPER(), LOWER(), PROPER(): Converts text to uppercase, lowercase, or proper case

Date and Time functions

NOW(), TODAY(): Returns the current date and time, or just the current date
DATE(): Converts a date in the form of text to a serial number that Excel recognizes as a date
DATEDIF(): Calculates the difference between two dates

Database functions

DSUM(): Adds the numbers in a field (column) of records in a database that match the specified conditions.
DAVERAGE(): Calculates the average of selected database entries.
DCOUNT(), DCOUNTA(): Counts the cells that contain numbers in a database, and DCOUNTA() counts non-blank cells.
DMIN(), DMAX(): Finds the minimum and maximum value, respectively, in a column of a list or database that matches the given conditions.

Search Functions

VLOOKUP(), HLOOKUP(): Looks for a value in a table array and returns a value in the same row or column
INDEX(): Returns the value of a cell in a table based on the row and column number
MATCH(): Searches for a specified item in a range and returns the relative position of that item

INDEX(block; Line; Column)

It allows you to find the value of a cell inside a certain block.

Is used more to retrieve a certain column from a record in a block.

Your block may be defined with a name or absolute coordinates in order that you can copy the formula to other place and keed the reference to the same block.

Page created and hosted by