Formulas and Functions
By Ted French, About.com Guide to Spreadsheets
This series of tips, tutorials, and articles covers how to create and use formulas and functions in Excel and other common spreadsheet programs.
- Spreadsheet Formulas
- Excel Date Functions
- Excel IF Functions
- Excel's Something - IF Functions
- Excel's VLOOKUP, HLOOKUP and TRANSPOSE Functions
- Excel SUM Functions
- Excel Count Functions
- Rounding Number Functions
- Excel Random Numbers Functions
- Excel AND and OR Functions
- Excel Text Functions
- Excel MAX, MIN, and AVERAGE Functions
- Excel MEDIAN and MODE Functions
- Excel LARGE, SMALL, and RANK Functions
- Excel's Information Functions
- Excel Trig Functions
- Excel Financial Functions
- Excel Functions that Divide and Multiply
- Excel 2003 Functions
Spreadsheet Formulas
Formulas are one of the most useful features of spreadsheets like Excel. Formulas can be as simple as adding two numbers or can be complex calculations needed for high end business projections.
- Excel Formulas
- Excel 2007 Formulas Step by Step Tutorial
- Excel 2003 Formulas Step by Step Tutorial
- Excel Math
Excel Date Functions
Dates are very important in spreadsheet programs like Excel. Most spreadsheets make use of dates in some way. The articles below list a number of date-related tutorials, each or which includes a step by step example of working with dates in Excel.
- Subtract Dates in Excel with Functions
- Excel 2007 TODAY Function
- Excel 2007 NOW Function
- Excel 2007 DATE Function
- Excel 2007 NETWORKDAYS Function
- Excel DATEDIF Function
- Excel 2007 WORKDAY Function
- Excel 2007 DAYS360 Function
- Date Function in Excel 2003
Excel IF Functions
The IF function is one of Excel’s most useful and most used functions. What it does, basically, is test to see whether a certain condition is true or false. If the condition is true, the function does one thing, if the condition is false, the function does something different. These articles cover creating and using the IF function in Excel spreadsheets and give examples of how this versatile can be utilized.
- Excel IF Function Step by Step Tutorial
- Excel 2007 IF Function
- Special Uses for the IF Function
- IF Function in Excel 2003 (video)
Excel's Something - IF Functions
The Something - IF functions are a group of functions that combine Excel's IF function with another function. In doing so, the flexibility of the IF function is extended making it easier to accomplish complex tasks in a worksheet.
- Excel 2007 SUMIF Function
- Excel 2007 SUMIFS Function
- Excel 2007 COUNTIF Function
- Excel 2007 COUNTIFS Function
- Excel 2007 AVERAGEIF Function
Excel's VLOOKUP, HLOOKUP and TRANSPOSE Functions
Excel's VLOOKUP and HLOOKUP Functions can be used to find specific data in a large database file. The TRANSPOSE function is used to convert columns of data into rows and rows of data in to columns.
- Excel 2007 VLOOKUP Step by Step Tutorial
- Excel 2007 VLOOKUP Function Quick Tutorial
- Excel 2007 HLOOKUP Function
Excel SUM Functions
The most common calculation carried out in spreadsheet programs like Excel is the adding up columns or rows of data. It is done so often, that Excel has a SUM function built into the program to simplify the task. The tutorials in this section cover using the SUM and AutoSUM functions in Excel. Each tutorial includes a step by step example.
Excel Count Functions
There are a number of Count Functions in Excel and each one does a slightly different job. One counts the cells containing numbers, another counts blank cells, and two others combine the COUNT function with Excel's IF function. The tutorials listed below cover various Count functions in Excel 2007 and Excel 2003. Each includes a step by step example.
Rounding Number Functions
The tutorials in this section cover the functions in Excel that are used when rounding numbers up or down. Each tutorial includes a step by step example of how to use it for rounding numbers in Excel.
Excel Random Numbers Functions
The tutorials in this section cover the functions in Excel that are used generate random numbers in Excel 2007. Each tutorial includes a step by step example of how to use it to generate random number in Excel 2007.
- Excel 2007 RAND Function
- Excel 2007 RANDBETWEEN Function
- Using Random Numbers in a Rolling Dice Tutorial
Excel AND and OR Functions
The usefulness of these functions in Excel is at its best when they are combined with one or more other functions, such as the IF function. In doing so they increase the flexibility of the other functions making it easier to accomplish complex tasks. The first tutorials in this section walk you through using the AND and OR functions in Excel 2007 and Excel 2003. The last tutorial - the rolling dice tutorial - combines the AND and OR function with the IF function to create a pair of working dice in Excel 2007.
- Excel 2007 AND Function
- Excel 2007 OR Function
- Excel 2003 OR Function
- Excel 2003 AND Function
- Excel Rolling Dice Tutorial
Excel Text Functions
Excel's Text Functions help you manage the text data in your spreadsheets.
- Excel 2007 CONCATENATE Function
- Excel 2007 LEFT Function
- Excel 2007 RIGHT Function
- Excel 2007 MID Function
- Excel 2007 TRIM Function
- Excel 2007 UPPER Function
- Excel 2007 LOWER Function
- Excel 2007 REPLACE Function
- Excel 2007 VALUE Function
Excel MAX, MIN, and AVERAGE Functions
The MAX and MIN Functions are used in Excel to find the largest and smallest numbers or the fastest and longest times -- The AVERAGE is used to find the average value of your data -- AVERAGEIF combines the AVERAGE function with the IF function. It allows you to find the average value of only those numbers meeting a certain condition.
- Excel 2007 MAX Function
- Excel 2007 AutoMAX Function
- Excel 2003 MAX Function
- Excel 2007 MIN Function
- Excel 2007 AutoMIN Function
- Excel 2003 MIN Function
- Excel 2007 AutoAVERAGE
- Excel 2007 AVERAGE Function
- Excel 2003 AVERAGE Function
- Excel 2007 AVERAGE IF
Excel MEDIAN and MODE Functions
The MEDIAN and MODE Functions are statistical functions that can be used to find the middle value in a group of numbers ( MEDIAN ) or the most frequently occurring value in a group of numbers ( MODE ).
Excel LARGE, SMALL, and RANK Functions
These functions will find how large or small a value is compared to other values in a list -- Use them to find the second oldest date, the third fastest time, or where a certain number ranks against other numbers in a list.
Excel's Information Functions
Excel's Information functions gives you information about the data in a cell or range of cells. This information includes whether the data is a number, the formatting applied to the cell, or even if the cell is empty.
- Excel 2007 ISNUMBER Function
- Excel 2007 ISBLANK Function
- Excel 2007 CELL Function
- Excel 2007 TYPE Function
- Excel 2007 INFO Function
Excel Trig Functions
Excel's list of functions includes the common Trig functions that can be used to find the sine, cosine, and tangent of an angle.
- Excel 2007 SIN Function
- Excel 2007 COS Function
- Excel 2007 TAN Function
- Excel 2007 RADIANS Function
- Excel 2007 DEGREES Function
Excel Financial Functions
Excel's Financial Functions can be used to help you determine changes in dollar value of investments and loans.
- Calculating Loan Payments Using Excel's PMT Function
- Calculating Future Value Using Excel's PMT Function
Excel Functions that Divide and Multiply
Excel's QUOTIENT and PRODUCT Functions can be used to perform division and multiplication in a spreadsheet. The tutorials listed below include a step by step example of using the QUOTIENT and PRODUCT Functions in Excel.
Excel 2003 Functions
Here you will find step by step tutorials, tips and shortcuts on how to use the common and less common functions available in Excel 2003.
