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.
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.
Determine the number from 0 to 7 repday of the week for a date.
Display the date that is x number of months after or before a given date.
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.
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.
Display the value on the right of the lookup value within a range.
Display the value below the lookup value within a range.
Display the value that correspond to the position of lookup value.
Return the range address, calculated based on a reference cell
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.
Find the total sum from two sets of values multipled by each other.
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.
Extract text within a cell. You can start from any character in the cell.
Extract text within a cell. You can start from any character in the cell.
Extract part of the text within a cell, from the left to the right.
Merge texts from different cells into one.
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.
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”.
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.
Identifies the scenario that will make zero losses in a project.
Calculates the number of periods required to recover an investment.
How much is a sum of money in the future worth now.
Calculates the net returns of an investment, calculated based on today’s value.
Determines the discounted rate that causes an investment to breakeven.
Click here to see the difference between excel formulas and functions
