Excel formulas (also known as Worksheet Functions)

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.

1. SUM – to add up the values in each cell within a given range,

2. COUNT – to count the number of cells containing values only in a given range,

3. 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),

4. MINIMUM – find the minimum value in a range,

5. MAXIMUM – find the maximum value in a range,

6. PRODUCT – multiply all the values in a range,

7. AVERAGE – the result when you sum up the values and then divide by the number of values included in the sum,

8. STANDARD DEVIATION for sample – the spread of a bell curve for a sample,

9. STANDARD DEVIATION for population – the spread of a bell curve for the entire population,

10. VARIANCE for sample – the square of the standard deviation for a sample,

11. VARIANCE for population – the square of the standard deviation for the entire population.

Date and Time formulas

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.

DAY 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.

Logical Test formulas

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 and Reference worksheet formulas

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.

Special Formulas

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 and data worksheet formulas

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 text within a cell. You can start from any character in the cell.

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

“=SUBSTITUTE(A1,”course”,”training”)”

in cell B5. Cell B5 will display the word “excel training”.

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 :

=TEXT(A1,”MM/YY”)

Financial formulas

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

Popular Courses

Useful Links

Links I found useful and wanted to share.

Search the website