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. You can find how the MATCH formula can be applied effectively in this great example.

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

"=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

New! Comments

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