Excel Formulas & Functions For Dummies book cover

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

page 1
page 2
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

Despite all the functions provided by Excel, you may need one that you just don't see offered. Excel lets you create your own functions by using VBA programming code; your functions show up in the Insert Function dialog box. Writing VBA code is not for everyone. But nonetheless, here is a short-and-sweet example. If you can conquer this, you may want to find out more about programming VBA. Who knows — maybe one day you'll be churning out sophisticated functions of your own! Make sure you are working in a macro-enabled workbook (one of the Excel file types). Follow along to create custom functions: Press Alt + F11. This gets you to the Visual Basic Editor, where VBA is written. You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box. Choose Insert→Module in the editor. You have an empty code module sitting in front of you. Now it's time to create your very own function! Type this programming code, shown in the following figure: Writing your own function Public Function Add(number1 As Double, number2 As Double) Add = number1 + number2 End Function Save the function. Macros and VBA programming can be saved only in a macro-enabled workbook. After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook. Return to Excel. Click the Insert Function button on the Formulas tab to display the Insert Function dialog box. Finding the function in the User Defined category Click OK. The Function Arguments dialog box opens, ready to receive the arguments. Isn't this incredible? It's as though you are creating an extension to Excel, and in essence, you are. Using the custom Add function This is a very basic example of what you can do by writing your own function. The possibilities are endless, but of course, you need to know how to program VBA. Macro-enabled workbooks have the file extension .xlsm.

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

This list includes the top ten Excel functions. The functions in this list are of the type that apply to a wide array of needs. You won't see a financial function or any advanced statistical function — just the basics — but knowing the functions here is essential to good Excel work. You can always refer here for a quick brush-up on how to use these important functions. SUM Adding numbers is one of the most basic mathematical operations, and so there is the SUM function, dedicated to doing just that. SUM takes up to 255 arguments. Each argument can be a single number or a range containing multiple numbers. That means SUM can add up a whole bunch of numbers! The syntax follows: =SUM(number 1, number 2, …) You can also use SUM with a range, as shown here: =SUM(A1:A12) You can also use SUM with more than one range, such as this: =SUM(A1:A12, B1:B12) AVERAGE Although technically a statistical function, AVERAGE is used so often that it deserves a place in the top ten functions. Everyone is interested in averages. What's the average score? What's the average salary? What's the average height? What's the average number of hours Americans watch TV? AVERAGE can take up to 255 arguments. Each argument can be a number or a range that contains numbers. The syntax follows: =AVERAGE(number 1 ,number 2 ,…) You can also use AVERAGE with a range, as shown here: =AVERAGE(A1:A12) You can also use AVERAGE with more than one range, such as this: =AVERAGE(A1:A12, B1:B12) COUNT COUNT counts the number of cells in a range that contain numbers. It does not provide any sum — just the count. For a list with ten numbers, for example, COUNT returns 10, regardless of what the numbers are. COUNT takes up to 255 arguments, which can be cell references, range references, or numbers themselves. COUNT ignores non-numeric values. If an argument to COUNT is A1:A10 but only two cells contain a number, COUNT returns 2. The syntax follows: =COUNT(cell reference 1, cell reference 2,…) You can also use COUNT with a range, as shown here: =COUNT(A1:A12) You can also use COUNT with more than one range, such as this: =COUNT(A1:A12, B1:B12) INT and ROUND The INT and ROUND functions both work by removing or reducing a number's decimal portion. They differ in exactly how they remove it. INT INT simply drops the decimal portion without rounding — that is, without regard to whether the number is closer to the next higher integer or the next lower integer. Be aware that INT always truncates to the next lower integer. For example, INT changes 12.05 to 12, but it also changes 12.95 to 12. Also, INT changes both –5.1 and –5.9 to –6, not to –5, because –6 is the next lower integer. INT takes a single number argument (as an actual number or a cell reference). The syntax follows: =INT(number or cell reference) ROUND On the other hand, the ROUND function lets you control how the decimal portion is handled. ROUND takes two arguments: the number to be manipulated and the number of decimal places to round to. This gives you more control. A number such as 5.6284 can become 5.628, 5.63, 5.6, or just 6. ROUND always rounds up or down to the nearest number of the next significant digit, so 5.628 becomes 5.63, not 5.62. ROUND turns 12.95 into either 12.9 or 13, depending on the setting of the second argument. Note that two functions — ROUNDUP and ROUNDDOWN — round in one direction only. The syntax for ROUND follows: =ROUND(number, number of decimal places to round to) The syntax for ROUNDUP and ROUNDDOWN is the same as ROUND: =ROUNDUP(number, number of decimal places to round to) =ROUNDDOWN(number, number of decimal places to round to) IF IF is a very handy function. It tests a condition and returns one of two results, depending on the outcome of the test. The test must return a true or false answer. For example, a test may be B25 > C30. If true, IF returns its second argument. If false, IF returns its third argument. IF is often used as a validation step to prevent unwanted errors. The most common use of this is to test whether a denominator is 0 before doing a division operation. By testing for 0 first, you can avoid the #DIV/0! error. One of the great things about IF is that the result can be a blank. This function is great when you want to return a result if the test comes out one way but not if the result is otherwise. The syntax follows: =IF(logical test, value if true, value if false) NOW and TODAY The NOW function returns the current date and time according to your computer's internal clock. TODAY returns just the date. If the date or time is wrong, it can't help you with that. A common use of NOW is to return the date and time for a printed report. You know, so a message such as Printed on 12/20/2015 10:15 can be put on the printed paper. A common use for TODAY is to calculate the elapsed time between a past date and today. For example, you may be tracking a project's duration. A cell on the worksheet has the start date. Another cell has a formula that subtracts that date from TODAY. The answer is the number of days that have gone by. NOW and TODAY take no arguments. The syntax for each follows: =NOW() =TODAY() HLOOKUP and VLOOKUP HLOOKUP and VLOOKUP both find a value in a table. A table is an area of rows and columns that you define. Both of these functions work by using a search value for the first argument that, when found in the table, helps return a different value. In particular, you use HLOOKUP to return a value in a row that is in the same column as the search value. You use VLOOKUP to return a value in a column that is in the same row as the search value. The syntax for these functions follows: =HLOOKUP(lookup value, table area, row, match type) =VLOOKUP(lookup value, table area, column, match type) ISNUMBER A rose is a rose and by any other name would smell as sweet, but numbers don't get off that easy. For example, 15 is a digit, but fifteen is a word. The ISNUMBER function tells you, flat-out true or false, if a value in a cell is a number (including the results of formulas). The syntax follows: =ISNUMBER(value) MIN and MAX MIN and MAX find the respective lowest or highest numeric value in a range of values. These functions take up to 255 arguments, and an argument can be a range. Therefore, you can test a large list of numbers simply by entering the list as a range. The syntax for these functions follows: =MAX(number1,number2,…) =MIN(number1,number2,…) You can also use MIN and MAX with a range, as shown here: =MAX(A1:A12) or with more than one range, such as this: =MAX(A1:A12, B1:B12) SUMIF and COUNTIF SUMIF and COUNTIF sum or count values, respectively, if a supplied criterion is met. This makes for some robust calculations. With these functions, it's easy to return answers for a question such as "How many shipments went out in October?" or "How many times did the Dow Jones Industrial Average close over 18,000 last year?" SUMIF takes three arguments: A range in which to apply the criteria The actual criteria The range from which to sum values A key point here is that the first argument may or may not be the same range from which values are summed. Therefore, you can use SUMIF to answer a question such as "How many shipments went out in October?" but also one such as "What is the sum of the numbers over 100 in this list?" The syntax of SUMIF follows: =SUMIF(range,criteria,sum_range) Note, too, that the third argument in SUMIF can be left out. When this happens, SUMIF uses the first argument as the range in which to apply the criteria and also as the range from which to sum. COUNTIF counts the number of items in a range that match criteria. This is just a count. The value of the items that match the criteria doesn't matter past the fact that it matches the criteria. But after a cell's value matches the criteria, the count of that cell is 1. COUNTIF takes just two arguments: The range from which to count the number of values The criteria to apply The syntax for COUNTIF follows: COUNTIF(range,criteria)

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

Article / Updated 03-22-2019

Excel’s IF function is like the Swiss Army knife of Excel functions. Really, it is used in many situations. Often, you can use Excel’s IF function with other functions. IF, structurally, is easy to understand. The Excel IF function takes three arguments: A test that gives a true or false answer. For example, the test "is the value in cell A5 equal to the value in cell A8" can have only one of two possible answers, yes or no. In computer talk, that's true or false. This is not a calculation, mind you, but a comparison. The data to be returned by the IF function if the test is true. The data to be returned by the IF function if the test is false. Sounds easy enough. Here are some examples: Function Comment =IF(D10>D20, D10, D20) If the value in D10 is greater than the value in D20, the value in D10 is returned because the test is true. If the value in D10 is not greater than — that is, smaller or equal to — the value in D20, the value in D20 is returned. If the values in D10 and D20 are equal, the test returns false, and the value in D20 is returned. =IF(D10>D20, "Good news!", "Bad news!") If the value in D10 is greater than the value in D20, the text “Good News!” is returned. Otherwise, “Bad News!” is returned. =IF(D10>D20, "", "Bad news!") If the value in D10 is greater than the value in D20, nothing is returned. Otherwise, “Bad News!” is returned. Note that the second argument is a pair of empty quotes. =IF(D10>D20, "Good news!", "") If the value in D10 is greater than the value in D20, “Good News!” is returned. Otherwise, nothing is returned. Note that the third argument is empty quotes. An important aspect to note about using IF: letting the second or third argument return nothing. An empty string is returned, and the best way to do this is to place two double quote marks together with nothing in the middle. The result is that the cell containing the IF function remains blank. IF, therefore, lets you set up two results to return: one for when the test is true and another for when the test is false. Each result can be a number, some text, a function or formula, or even blank. As you see in the preceding example, a common use of IF is to see how two values compare and return either one value or the other, depending on how you set up the test in the first argument. IF is often used as a validation check to prevent errors. Suppose that you have a financial worksheet that uses a variable percentage in its calculations. The user must enter this percentage each day, but it must never be greater than 10 percent. To prevent the chance of errors, you could use the IF function to display an error message in the adjacent cell if you mistakenly enter a value outside the permitted range. Assuming that the percentage is entered in cell A3, here's the required IF function: =IF(A3>.1, "ERROR: the % in A3 IS TOO LARGE", "") The following image shows how IF can be put to good use in a business application. A fictitious store shop — Ken's Guitars (kinda snappy, don’t you think?) — keeps tabs on inventory in an Excel worksheet. Column D shows the inventory levels, and column E shows the reorder levels. It works this way: When a product's inventory level is the same or less than the reorder level, it is time to order more of the product. The cells in column F contain a formula. The Excel formula in cell F8 is =IF(D8<=E8,"ORDER",""). It says that if the number of Stratoblaster 9000 guitars in stock is the same or less than the reorder level, return Order. If the number in stock is greater than the reorder level, return nothing. Nothing is returned because three are in stock and the reorder level is two. In the next row, the number of Flying Xs is equal to the reorder level; therefore, cell F9 displays Order. Using Excel’s IF function is easy. Follow these steps: Enter two values in a worksheet.These values should have some meaning to you, such as the inventory levels example shown above. Click the cell where you want the result to appear. Type =IF( to start the function. Decide what test you want to perform.You can see whether the two values are equal; whether one is larger than the other; whether subtracting one from the other is greater than, equal to, or less than 0; and so on. For example, to determine whether the first value equals the second value, click the first cell (or enter its address), enter an equal sign (=), and then click the second cell (or enter its address). Type a comma (,). Enter the result that should appear if the test is true. For example, enter “The values are equal”. Text must be enclosed in quotes. Type a comma (,). Enter the result that should appear if the test is false.For example, enter “The values are not equal”. Type a ) and press Enter. The Excel IF function can do a whole lot more. Nested IF functions give you a lot more flexibility in performing tests on your worksheet data. A bit of perseverance is necessary to get through this. Nested means that you can place an IF function inside another IF function. That is, the inner IF is placed where the true or false argument in the outer IF goes (or even use internal IFs for both of the arguments). Why would you do this? Here’s an example: The other night, we were deciding where to go for dinner. we were considering Italian and decided that if we went to an Italian place and it served manicotti, we would have manicotti. Otherwise, we decided to eat pizza. Logically, this decision looks like this: If the restaurant is Italian, then If the restaurant serves manicotti, then we will have manicotti else we will have pizza This looks a lot like programming code. The End If statements have been left off on purpose to prevent confusion because the IF function has no equivalent value. That's it! Make note that the inner IF statement has a result for both the true and false possibilities. The outer IF does not. Here is the structure as nested Excel IF statements: =IF(Restaurant=Italian, IF(Restaurant serves manicotti, "manicotti", "pizza"), "") If the restaurant were not Italian, it wouldn’t matter what the choice was (as indicated by the third argument of the outer IF being empty). You can nest up to 64 IF statements, although things are likely to get very complicated once you go beyond 4 or 5. You can apply a nested IF statement to increase the sophistication of the inventory worksheet from above. The following image has an additional column: Hot Item. A Hot Item can take three forms: If the inventory level is half or less of the reorder level and the last sale date is within the last 30 days, this is a Hot Item. The point of view is that in 30 days or less the stock sold down to half or less than the reorder level. This means that the inventory is turning over at a fast pace. If the inventory level is half or less of the reorder level and the last sale date is within the last 31–60 days, this is a Warm Item. The point of view is that in 31–60 days the stock sold down to half or less than the reorder level. This means that the inventory is turning over at a medium pace. If neither of the preceding two conditions is met, the item is not assigned any special status. There are Hot Items, and there are Warm Items. Both must meet the common criterion that the inventory is 50 percent or less of the reorder level. Only after this first condition is met does the second criterion — the number of days since the last order — come into play. Sounds like a nested IF to me! Here is the formula in cell G8: =IF(D8<=([email protected]@ts0.5),IF(NOW()-C8<=30,"HOT!",IF(NOW()-C8<=60,"Warm!","")),"") Okay, take a breath. The outer IF tests whether the inventory in column D is equal to or less than half (50 percent) of the reorder level. The piece of the formula that does that is =IF(D8<=([email protected]@ts0.5). This test, of course, produces a true or false answer. If it is false, the false part of the outer IF is taken (which is just an empty string found at the end of the formula: ,"")). That leaves the whole middle part to wade through. Stay with it! If the first test is true, the true part of the outer IF is taken. It just so happens that this true part is another IF function: IF(NOW()-C8<=30,"HOT!",IF(NOW()-C8<=60,"Warm!","")) The first Excel argument of the inner IF tests whether the number of days since the last order date (in column C) is less than or equal to 30. You do this by subtracting the last order date from today, as obtained from the NOW function. If the test is true, and the last order date is within the last 30 days, HOT! is returned. A hot seller indeed! If the test is false … wait, what's this? Another IF function! Yes: an IF inside an IF inside an IF. If the number of days since the last order date is greater than 30, the next nested IF tests whether the number of days is within the last 60 days: IF(NOW()-C8<=60 If this test is true, Warm! is returned. If the test is false, nothing is returned. A few key points about this triple-level IF statement: The IF that tests whether the number of elapsed days is 30 or fewer has a value to return if true (HOT!) and a value to return for false (whatever is returned by the next nested IF). The outer IF and the innermost IF return nothing when their test is false. On the surface, the test for 60 or fewer days also would catch a date that is 30 days or fewer since the last order date. This is not really what is meant to be. The test should be whether the number of elapsed days is 60 or fewer but more than 30. You do not have to actually spell it out this way, because the formula got to the point of testing for the 60-day threshold only because the 30-day threshold already failed. Gotta watch out for these things!

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

The Excel ADDRESS function takes a row number and a column number as arguments and returns a standard cell reference (cell address). For example, if you pass the row number 4 and the column number 3, the function returns C4. Excel’s ADDRESS function can return an absolute or relative reference in either of Excel's two reference formats. Before you get to the details, here’s a quick review of the differences between absolute and relative cell references: A relative reference is expressed as just the column letter and row number (for example, M290). When you copy a formula that contains a relative cell reference, the reference — the row number and the column letter — is adjusted to reflect the location to which you copied the formula. An absolute reference has a dollar sign in front of the column letter and the row number (for example, $M$290). When you copy a formula that contains an absolute cell reference, the reference does not change. A mixed reference has a dollar sign in front of the column letter or the row number (for example, $M290 or M$290). When you copy a formula that contains a mixed cell reference, the part of the reference with the dollar sign does not change, but the other part does. The following image shows a worksheet in which entering a formula with a relative cell reference causes a problem. Totals are the result of adding the tax to the amount. The tax is a percentage (0.075) for a 7.5 percent tax rate. This percentage is in cell C1 and is referenced by the formulas. The first formula that was entered is in cell C7 and looks like this: =B7*(1 + C1). The formula in cell C7 works correctly. It references cell C1 to calculate the total. But if you use the fill handle to copy the formula from cell C7 to cells C8 and C9, there's a problem. The reference to cell C1 changed to cell C2 and C3. Because these cells are empty, the results in cells C8 and C9 are incorrect; they are the same as the amounts to the left. (No tax is added.) To better understand, column D displays the formulas that are in column C. When the formula in cell C7 was dragged down, the C1 reference changed to C2 in cell C8, and to C3 in cell C9. Often, this is what you want — for Excel to automatically change cell references when a formula is copied. But sometimes, as in this situation, it is not what you want. You need an absolute cell reference. The formula in cell C17 is almost identical to the one in cell C7 except that the reference to cell C1 has been made row absolute by placing a dollar sign in front of the row number. The formula in cell C17 looks like this: =B17*(1 + C$1). When this formula was dragged down into C18 and C19, the reference was not adjusted but stayed pointing at cell C1. Note that in this example, only the row part of the reference is made absolute. That's all that is necessary. You could have made the reference completely absolute by doing this: =B17*(1 + $C$1). The result would be the same, but it's not required in this example. Put a dollar sign in front of the column letter of a cell reference to create an absolute column reference. Put a dollar sign in front of the row number to create an absolute row reference. Excel supports two cell reference styles: the good old A1 style and the R1C1 style. R1C1 style uses a numerical system for both the row and the column, such as this: R4C10. In this example, R4C10 means row 4 column 10. To change the cell reference style, choose File  →    Options and check the R1C1 reference style in the Working with Formulas area on the Formulas tab. Using the R1C1 format also forces the columns on the worksheet to display as numbers instead of the lettering system. This is useful when you’re working with a large number of columns. For example, column CV positionally is the 100th column. Remembering 100 is easier than remembering CV. To get back to the Excel ADDRESS function, it takes up to five arguments: The row number of the reference The column number of the reference A number that tells the function how to return the reference. The default is 1, but it can be 1 for full absolute 2 for absolute row and relative column 3 for relative row and absolute column 4 for full relative A value of 0 or 1 to tell the function which reference style to use: 0 uses the R1C1 style. 1 (the default if omitted) uses the A1 style. A worksheet or external workbook and worksheet reference Only the first two arguments are required: the row number and column number being addressed. The function returns the specified reference as text. Using the Excel ADDRESS Function Syntax Result Comment =ADDRESS(5,2) $B$5 Only the column and row are provided as arguments. The function returns a full absolute address. =ADDRESS(5,2,1) $B$5 When a 1 is used for the third argument, a full absolute address is returned. This is the same as leaving out the third argument. =ADDRESS(5,2,2) B$5 When a 2 is used for the third argument, a mixed reference is returned, with the column relative and the row absolute. =ADDRESS(5,2,3) $B5 When a 3 is used for the third argument, a mixed reference is returned, with the column absolute and the row relative. =ADDRESS(5,2,4) B5 When a 4 is used for the third argument, a full relative reference is returned. =ADDRESS(5,2,1,0) R5C2 When the fourth argument is false, an R1C1-style reference is returned. =ADDRESS(5,2,3,0) R[5]C2 This example tells the function to return a mixed reference in the R1C1 style. =ADDRESS(5,2,1,,"Sheet4") Sheet4!$B$5 The fifth argument returns a reference to a worksheet or external workbook. This returns an A1-style reference to cell B5 on Sheet 4. =ADDRESS(5,2,1,0,"Sheet4") Sheet4!R5C2 This returns an R1C1-style reference to B5 on Sheet 4. Use Excel's ADDRESS function this way: Click a cell where you want the result to appear. Type =ADDRESS( to start the function. Enter a row number, a comma (,), and a column number. You can also enter references to cells where those values are located. If you want the result to be returned in a mixed or full reference, enter a comma (,) and the appropriate number: 2, 3, or 4. If you want the result to be returned in R1C1 style, enter a comma (,) and enter 0. If you want the result to be a reference to another worksheet, enter a comma and put the name of the worksheet in double quote marks. If you want the result to be a reference to an external workbook, enter a comma (,) and enter the workbook name and worksheet name together. The workbook name goes in brackets, and the entire reference goes in double quote marks, such as this: "[Book1]Sheet2". Type a ) and press Enter. Instead of entering a row number and column number directly in ADDRESS, you can enter cell references. However, the values you find in those cells must evaluate to numbers that can be used as a row number and column number.

View Article
How to Convert a Date from Text with Excel’s DATEVALUE Function

Article / Updated 01-31-2019

You may have data in your Excel worksheet that looks like a date but is not represented as an Excel date value. For example, if you enter 01-24-18 in a cell, Excel would have no way of knowing whether this is January 24, 2018, or the code for your combination lock. If it looks like a date, you can use the Excel DATEVALUE function to convert it to an Excel date value. In practice, any standard date format entered into a cell is recognized by Excel as a date and converted accordingly. However, there may be cases, such as when text dates are imported from an external data source or data is copied and pasted into Excel, for which you need DATEVALUE. Why not enter dates as text data? Although they may look fine, you can’t use them for any of Excel’s powerful date calculations without first converting them to date values. Excel’s DATEVALUE function recognizes almost all commonly used ways that dates are written. Here are some ways that you may enter August 14, 2018: 8/14/18 14-Aug-2018 2018/08/14 DATEVALUE can convert these and several other date representations to a date serial number. After you’ve converted the dates to a date serial number, you can use the dates in other date formulas or perform calculations with them. To use Excel’s DATEVALUE function, follow these steps: Select the cell where you want the date serial number located. Type =DATEVALUE( to begin the function entry. Click the cell that has the text format date. Type a ) and press Enter. The result is a date serial number unless the cell where the result is displayed has already been set to a date format. The following image shows how some nonstandard dates in column A have been converted to serial numbers with the Excel DATEVALUE function in column B. Then column C displays these serial numbers formatted as dates. Do you notice something funny in the image above? Normally, you aren’t able to enter a value such as the one in cell A4 — 02-28-10 — without losing the leading 0. The cells in column A had been changed to the Text format. This format tells Excel to leave your entry as is. The Text format is one of the choices in the Category list in the Format Cells dialog box. Note also that the text date in cell A8, Feb 9 14, could not be converted by DATEVALUE, so the function returns the error message #VALUE#. Excel is great at recognizing dates, but it’s not perfect! In cases such as this, you have to format the date another way so DATEVALUE can recognize it.

View Article
page 1
page 2