Excel formulas are also commonly known as worksheet functions. Worksheet functions are used to calculate numbers captured in one or more worksheets. The primary worksheet functions are used for addition (positive and negative numbers), multiplication, counting, finding maximum and minimum values in a range. Each worksheet function must start with an equal sign.

The primary calculation in Excel falls into 11 basic categories as shown below. They can be found in the Subtotal function and also in Pivot Tables.

- SUM - to add up the values in each cell within a given range,
- COUNT - to count the number of cells containing values only in a given range,
- COUNT - to count the number of cells containing values, text, blanks, etc in a givne range. It will count a cell as one unit as long as the cell is not empty (a cell with a space is not empty),
- MINIMUM - find the minimum value in a range,
- MAXIMUM - find the maximum value in a range,
- PRODUCT - multiply all the values in a range,
- AVERAGE - the result when you sum up the values and then divide by the number of values included in the sum,
- STANDARD DEVIATION for sample - the spread of a bell curve for a sample,
- STANDARD DEVIATION for population - the spread of a bell curve for the entire population,
- VARIANCE for sample - the square of the standard deviation for a sample,
- VARIANCE for population - the square of the standard deviation for the entire population.

Beside the above basic calculations, Excel formulas can also be
used calculate days and time. This can only be achieved if you understand how
**Excel stores dates and time.**

**DATE Formula**

Generates the serial number of a date based the input year, month and day. Very useful if you need to insert a
date into another formula.

**WEEKDAY Formula**

Determine the number from 0 to 7 repday of the week for a date.

**EDATE Formula**

Display the date that is x number of months after or before a given date.

**DATEDIF Formula**

To determine the number of completed months or years between 2 dates. It is very useful for
calculating age or the anniversaries of employees.

The logical formulas do only one thing, to return the result as TRUE or FALSE. They are very powerful and are commonly used to evalation data within a range.

**IF**

Determine the value or text to display under a specific condition.

Lookup formulas are very good for cleaning up and managing data. They are very efficient when it comes to finding data in a sea of information.

**VLOOKUP or Vertical Lookup**

Display the value on the right of the lookup value within a range.

**HLOOKUP or Horizontal Lookup**

Display the value below the lookup value within a range.

**LOOKUP**

Display the value that correspond to the position of lookup value.

**OFFSET**

Return the range address, calculated based on a reference cell

**MATCH**

Returns the position of the lookup value found in a range.

**CHOOSE Formula**

CHOOSE(2,"a","b","c")
Returns the value in a given position.

The special formulas below can perform multiple calculation and is reserved for advanced users of Excel.

**SUMPRODUCT**

Find the total sum from two sets of values multipled by each other.

**SUBTOTAL**

Can perform 11 different calculations and works hand in hand with auto-filter function.

Text formulas as the name imples is used to manage text in Excel. Some of the formulas could also be used to manage values.

**MID**

Extract text within a cell. You can start from any character in the cell.

**RIGHT**

Extract part of the text within a cell from the extreme right to the left.

**LEFT**

Extract part of the text within a cell, from the left to the right.

**CONCATENATE**

Merge texts from different cells into one.

**LEN**

To find out how many characters (including spaces) there are in a cell. For example, if cell
A1 contains the word "**excel courses**", entering the LEN formula (without quotes) "=LEN(A1)"
into cell B1 will return the number 13, because there are 13 characters in the word including
the space between the word excel and courses.

**SUBSTITUTE**

To present text or value from another cell with certain text or values replaced. For example, you can
replace the word "course" in "excel course" displayed in Cell A1 with the word
"training" and present it in cell B5 by entering the SUBSTITUTE formula without quotes

**TEXT**

To present a number as text and in a pre-defined format. This formula is very useful when
you want to present
excel dates
(they are actually numbers) in some other date format.

For example, you can convert the date 16-Mar-09 displayed in cell A1 to 03/09 by making
reference to cell A1 and giving the format code (including the quotes) "MM/YY" in the
TEXT formula as shown :

The following formulas are reserved for those who have some basic foundation in finance.

**Breakeven Analysis**

Identifies the scenario that will make zero losses in a project.

**Payback Period**

Calculates the number of periods required to recover an investment.

**Present Value (PV)**

How much is a sum of money in the future worth now.

**Net Present Value (NPV)**

Calculates the net returns of an investment, calculated based on today's value.

**Internal Rate Of Return (IRR)**

Determines the discounted rate that causes an investment to breakeven.

Click here to see the difference between excel formulas and functions

•

## New! Comments

Have your say about what you just read! Leave me a comment in the box below.