Section # 2: Functions and Formulas in Excel

Excel offers you 328 functions and 17 signs. Of these only 20% are very useful the rest is either obsolete, useless or very specialized. The functions are the words you use to talk in Excel and the formulas are the sentences. If you can say it in English there is an Excel formula for it. This chapter begins with 25 powerful tips on functions and formulas (using the F4 key to add dollar signs to your formulas, formulas that refer to other spreadsheets on the Internet, getting rid of links). You will then find instructions to develop step by step simple and very complex formulas using the 16 important signs and the 40 important functions (hundreds of examples). Start with the basic (SUM, +, -, /, *) to the most important (SUMPRODUCT and INDEX/MATCH) and the other powerful date and time, text, financial, mathematical functions and important functions from the other categories. The six most useful signs and functions after the basics are the dollar sign ($), SUMPRODUCT, INDEX and MATCH, IF and ISERROR. You will find in this chapter extensive tutorials on each of them plus hundreds of examples on the other important ones. There is even a functions that Excel forgot to include in its drop-down list it is the DATEDIF function that caculates the number of years, months or days between two dates.

Table of Contents

Lesson 1: Introduction and General Tips

This chapter begins with 25 powerful tips on functions and formulas (using the F4 key to add dollar signs to your formulas, formulas that refer to other spreadsheets on the Internet, getting rid of links).

Lesson 2: Absolute and Relative References

When you want to copy/paste a formula it is important to know about absolute and relative references.

Lesson 3: The ISERROR Function

When a formula refers to a cell in which you have another formula, always use the ISERROR function to avoid trashing the last formula with a "#DIV/0" or a "#VALUE" or a "#N/A".

Lesson 4: The SUMPRODUCT Function in Excel

When users discover the SUMPRODUCT function Excel really becomes the ultimate reporting program. You can automate all your reports (enter new data and the report is automatically refreshed). Simple to use and extremely powerful.

Lesson 5: The INDEX/MATCH Formula in Excel

The new way of automating invoices, packing slips, orders or developing reports based on two different sets of data. Enter a name and its address appears automatically in the other cell, enter a part number and its description appears in the other cell.

Lesson 6: Excel Signs and Formulas

Excel offers you 17 signs to develop formulas. From plus to minus all the way to the ampersand (&) to assemble strings of text and the very important dollar sign ($) without which you cannot really copy/paste formulas

Lesson 7: Dates and Times Functions in Excel

These functions are not very complex but beware of the cell's formats that you are using. Discover the function that Excel doesn't even offer you when you use "Insert Function" the DATEDIF function to calculate the difference between two date in years, months or days.

Lesson 8: Information Functions in Excel

When you don't want your formula to return an error message (#DIV/0, #NAME and others) you protect them with the ISERROR function. Discover how and discover the other useful information functions in Excel: CELL, ISNUMBER and ISTEXT.

Lesson 9: Financial Functions in Excel

Here are a few good examples of financial functions in Excel even though finance people use more of the other functions to analyze their data. Date and time functions and the magic formulas developed with SUMPRODUCT and INDEX/MATCH are key functions for financial analysts.

Lesson 10: Logical Functions in Excel

The problem is not the functions (IF, AND and OR) it is the logic. Let's work on both. Learn how to work with on or more conditions, to work with numbers and to work with texts.

Lesson 11: LookUp & Reference Functions in Excel

Find values in tables. Discover the powerful INDEX/MATCH formulas and the old VLOOKUP. Automate your invoices and develop reports referring to two different sets of data.

Lesson 12: Math & Trig Functions in Excel

From SUM to the most useful and powerful function in Excel; SUMPRODUCT.

Lesson 13: Statistical Functions in Excel

There are 80 functions in the statistical category but people who work with business data only use 4 of them: AVERAGE, COUNT, MAX and MIN. Here are examples of all four of them used by themselves or in combination with other functions.

Lesson 14: Text Functions in Excel

Functions and formulas that you will use to reformat data coming from an outside source and make them usable within your spreadsheets. You will also use these to reformat your Excel data so that you can load them in batch into your centralized database or application (accounting, manufacturing, sales...) instead of entering them manually ONE by ONE.

Other Pages on Excel Functions and Formulas