# Excel Formulas & Functions For Dummies

By: Ken Bluttman Published: 12-21-2021

Unlock the power of Excel with a step-by-step roadmap to its formulas and functions

There's a Swiss Army knife in your digital toolbox that can multiply your productivity and make you the smartest guy or gal in almost any room. It's called Microsoft Excel.

If you're like most people, you've barely scratched the surface of what this powerful tool's hundreds of built-in functions can do. But with a little help from Excel Formulas & Functions For Dummies, you'll soon be organizing, analyzing, and interpreting data like a pro.

For those who don't know the difference between a spreadsheet and a bedsheet, the book gets you up to speed with formula and function basics first. But you can also skip ahead to the fancy stuff and learn about working with probabilities, significance tests, and lookup functions.

This easy-to-use Excel formulas and functions survival guide shows you how to:

• Work with financial functions like PMT, PPMT, NPER, RATE, and PV
• Calculate mean, median, mode, standard deviation, and many more statistical functions
• Troubleshoot formulas for common errors and validate your data to avoid mistakes
• Work with dates, times, logic operators, conditions, and basic and advanced mathematical functions

You don't need a degree in data science or advanced mathematics to take advantage of the full functionality and flexibility of Microsoft Excel. Let Excel Formulas & Functions For Dummies show you how to transform this unassuming program into the most useful tool in your toolbox.

## Articles From Excel Formulas & Functions For Dummies

19 results
19 results
Excel Formulas and Functions For Dummies Cheat Sheet

Cheat Sheet / Updated 01-27-2022

It’s easy to use Excel for many of your day-to-day number-crunching tasks, like determining your business’s average sale, computing classroom grades, or forecasting college expenses. Use this handy Cheat Sheet to discover great functions and tips to help you get the most out of Excel.

View Cheat Sheet
How to Create Custom Excel Functions

Article / Updated 01-07-2022

View Article
What Goes into an Excel Function

Article / Updated 01-07-2022

Most Excel functions take inputs — called arguments or parameters — that specify the data the function is to use. Some functions take no arguments, some take one, and others take many; it all depends on the function. The argument list is always enclosed in parentheses following the function name. If there's more than one argument, the arguments are separated by commas. Look at a few examples: Function Comment =NOW() Takes no arguments. =AVERAGE(A6,A11,B7) Can take up to 255 arguments. Here, three cell references are included as arguments. The arguments are separated by commas. =AVERAGE(A6:A10,A13:A19,A23:A29) In this example, the arguments are range references instead of cell references. The arguments are separated by commas. =IPMT(B5, B6, B7, B8) Requires four arguments. Commas separate the arguments. Some functions have required arguments and optional arguments. You must provide the required ones. The optional ones are, well, optional. But you may want to include them if their presence helps the function return the value you need. The IPMT function is a good example. Four arguments are required, and two more are optional.

View Article
Using the Excel Function Arguments Dialog Box to Edit Functions

Article / Updated 01-07-2022

Excel makes entering functions with the Insert Function dialog box easy. But what do you do when you need to change a function that has already been entered in a cell? What about adding arguments or taking some away? There is an easy way to do this! Follow these steps: Click the cell with the existing function. Click the Insert Function button. The Function Argument dialog box appears. This dialog box is already set to work with your function. In fact, the arguments that have already been entered in the function are displayed in the dialog box as well! Add, edit, or delete arguments, as follows: To add an argument (if the function allows), use the RefEdit control to pick up the extra values from the worksheet. Alternatively, if you click the bottom argument reference, a new box opens below it, and you can enter a value or range in that box. To edit an argument, simply click it and change it. To delete an argument, click it and press the Backspace key. Click OK when you're finished. The function is updated with your changes.

View Article
Nesting Functions in Excel

Article / Updated 01-07-2022

A nested function is tucked inside another Excel function as one of its arguments. Nesting functions let you return results you would have a hard time getting otherwise. The following figure shows the daily closing price for the Standard & Poor's 500 for the month of September 2004. A possible analysis is to see how many times the closing price was higher than the average for the month. Therefore, you need to calculate the average before you can compare any single price. Embed the AVERAGE function inside another function to calculate the average first. When a function is nested inside another, the inner function is calculated first. Then that result is used as an argument for the outer function. The COUNTIF function counts the number of cells in a range that meet a condition. The condition in this case is that any single value in the range is greater than (>) the average of the range. The formula in cell D7 is =COUNTIF(B5:B25, ">" & AVERAGE(B5:B25)). The AVERAGE function is evaluated first; then the COUNTIF function is evaluated, using the returned value from the nested function as an argument. Nested functions are best entered directly. The Insert Function dialog box does not make it easy to enter a nested function. Try one. In this example, you use the AVERAGE function to find the average of the largest values from two sets of numbers. The nested function in this example is MAX. You enter the MAX function twice within the AVERAGE function. Follow these steps: Enter a few different numbers in one column. Enter a few different numbers in a different column. Click an empty cell where you want the result to appear. Type =AVERAGE( to start the function entry. Type MAX(. Click the first cell in the second set of numbers, press the mouse button, and drag over all the cells of the first set. The address of this range enters into the MAX function. Enter a closing parenthesis to end the first MAX function. Enter a comma (,). Once again, type MAX(. Click the first cell in the second set of numbers, press the mouse button, and drag over all the cells of the second set. The address of this range enters into the MAX function. Enter a closing parenthesis to end the second MAX function. Enter a ). This ends the AVERAGE function. Press Enter. This figure shows the result of your nested function. Cell C14 has this formula: =AVERAGE(MAX(B4:B10),MAX(D4:D10)). Getting a result from nested functions. When you use nested functions, the outer function is preceded with an equal sign (=) if it is the beginning of the formula. Any nested functions are not preceded with an equal sign. You can nest functions up to 64 levels.

View Article
10 Excel Functions You Should Know

Article / Updated 01-07-2022

View Article
Using the Excel IF Function: Testing on One Condition

Article / Updated 03-22-2019

View Article
How to Use Excel’s OFFSET Function

Article / Updated 01-31-2019

Excel’s OFFSET function lets you get the address of the cell that is offset from another cell by a certain number of rows and/or columns. For example, cell E4 is offset from cell B4 by three columns because it is three columns to the right. The Excel OFFSET takes up to five arguments. The first three are required: A cell address or a range address: Named ranges are not allowed. The number of rows to offset: This can be a positive or negative number. Use 0 for no row offset. The number of columns to offset: This can be a positive or negative number. Use 0 for no column offset. The number of rows in the returned range: The default is the number of rows in the reference range (the first argument). The number of columns to return: The default is the number of columns in the reference range. If you omit the last two arguments, OFFSET returns a reference to a single cell. If you include a value greater than 1 for either or both, the function's return references a range of the specified size with the top-left cell at the specified offset. The following image shows some examples of using Excel’s OFFSET function. Columns A through C contain a ranking of the states in the United States by size in square miles. Column E shows how OFFSET has returned different values from cells that are offset from cell A3. Some highlights follow: Cell E4 returns the value of cell A3 because both the row and column offset is set to 0: =OFFSET(A3,0,0). Cell E7 returns the value you find in cell A1 (the value also is A1). This is because the row offset is –2. From the perspective of A3, minus two rows is row number 1: =OFFSET(A3,-2,0). Cell E8 displays an error because OFFSET is attempting to reference a column that is less than the first column: =OFFSET(A3,0,-2). Cell E10 makes use of the two optional OFFSET arguments to tell the SUM function to calculate the sum of the range C4:C53: =SUM(OFFSET(A3,1,2,50,1)). Here's how to use the OFFSET function: Click a cell where you want the result to appear. Type =OFFSET( to start the function. Enter a cell address or click a cell to get its address. Type a comma (,). Enter the number of rows you want to offset where the function looks for a value. This number can be a positive number, a negative number, or 0 for no offset. Type a comma (,). Enter the number of columns you want to offset where the function looks for a value. This can be a positive number, a negative number, or 0 for no offset. Type a ) and press Enter. OFFSET is another of those functions that can be used alone but is usually used as part of a more complex formula.

View Article
How to Use Excel’s ADDRESS Function

Article / Updated 01-31-2019