Put the power of Excel functions to work in your formulas There are more than …. The complete guide to Excel , from Mr. Spreadsheet himself Whether you are just starting out …. Skip to main content. Downloads Follow the instructions to download this book's companion files or practice files. Click the Download button below to start the download. If prompted, click Save.
Locate the. Right-click the file, click Extract All, and then follow the instructions. Errata We've made every effort to ensure the accuracy of this book and its companion content. The work is protected by local and international copyright laws and is provided solely for the use of instructors in teaching their courses and assessing student learning.
You have successfully signed out and will be required to sign back in should you need to download more resources. Description Master business modeling and analysis techniques with Microsoft Excel , and transform data into bottom-line results. Written by award-winning educator Wayne Winston, this hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables—and how to effectively build a relational data source inside an Excel workbook.
Series This product is part of the following series. Business Skills. New to This Edition. Updated for Excel and its added capabilities. Share a link to All Resources. Fortune corporations and government agencies, including the U.
You can follow along with. If you want to, you can use these templates to work directly with Excel and. If you read this book, you will learn how to predict. These examples are interesting and fun, and they also. To follow the examples in this book you do not need to be an Excel guru. Basically, the two. You will be surprised. This is the. This book features a companion website that makes available to you all the sample files you.
The workbooks and templates are organized in folders named for. The answers to all chapter-ending problems in the book are also included with. Each answer file is named so that you can identify it easily. For example, the. Display the detail page in your Web browser, and follow the instructions for downloading.
If you need. At Microsoft Press, your satisfaction is our top priority, and your feedback our most. The survey is short, and we read every one of your comments and ideas. Thanks in advance. I am eternally grateful to Jennifer Skoog and Norm Tonina, who had faith in me and first. Jennifer in particular was instrumental. Army also helped me refine my thoughts on teaching data analysis and modeling with Excel.
Microsoft Press editors. Rosemary Caperton and Devon Musgrave helped shepherd the project to completion. School of Business. Many of them have taught me things I did not know about Excel. Alex Blanton, formerly of Microsoft Press, championed this project at the start and shared my.
Finally, my lovely and talented wife, Vivian, and my wonderful children, Jennifer and Gregory,. You can change the order in which the tabs appear by. If you. For example, if you clear Design under. New Tab button allows you to create a new tab, and the New Group button lets you create. You probably use some Excel comm and s much more often than others.
Having to switch. The Quick Access. Toolbar see Figure allows you to collect your favorite comm and s in one place. You can add a comm and to the Quick Access Toolbar simply by right-clicking the comm and. You can also add comm and s by clicking File in. Next click Options, and then display the Customize. After choosing a comm and you want. Of course, the Move Up and Move Down arrows let you. You can remove any comm and from the Quick. People sometimes have trouble finding comm and s that appeared in earlier versions of Excel.
After scrolling down pressing the P key several. Sparklines are small charts or graphs that fit in a single cell. Sparklines let you place a. The sparklines make it clear that each branch is busiest on Monday and Friday. Sparklines are. PivotTables are probably the single most used tool for summarizing data. PivotTables let you. Summarize Data. The Name and Product slicers in Figure ensure that total sales for each month are. In the past, it has been difficult to create PivotTables.
Excel contains a much improved version of the Solver that allows you to use many important. With previous. Excel introduced the Office button. In Excel , the Office button has been replaced. The File tab is located at the left end of the ribbon. After selecting File, you. You can see that the File tab combines the Print and File menus from previous versions of.
Also, selecting Options lets you perform a variety of tasks, such as customizing the. In Excel these tasks except for. INTL functions see Chapter 7,.
The accuracy of many financial functions see. After choosing Insert, click the drop-down arrow to the right of Equation. Of course, if after. If you right-click a range of cells and select Paste Special see Chapter 14 for a discussion. Clicking an option lets you see a preview of how your spreadsheet will appear if you choose. Can I. Can we. The Name box shown in Figure is located directly above the label for column A. To see. FigURE You can create a range name by selecting the cell range you want to name and then typing the.
For example, suppose you want to name cell F3 east and cell F4 west. See Figure and the. Simply select cell F3, type east in the Name box, and then press Enter. If you now reference cell F3 in. Suppose you want to assign a rectangular range of cells such as A1:B4 the name Data. Now a. See the file. For example, in Figure and the file Noncontig.
To assign this. Hold down. Now release the Ctrl. Using Noncontig in any. For example, entering. Figure shows a subset of this data. We would like to name each cell in the range B6:B To do this, select the range A6:B55, and click Create From. FigURE By naming the cells that contain state sales with state abbreviations, you can use the abbreviation.
Thus, B6 is assigned the range name AL, B7. Note that creating these range names in the Name box would have. Simply type range1 in the Name box, and then point to the range or type.
If you now click the Name arrow, the name range1 and any other ranges you have created. Simply open the Name Manager by selecting the Formulas tab and then clicking. Name Manager. You will now see a list of all range names. For example, for the file States. To edit any range name, simply double-click the range name or select the range name and.
Then you can change the name of the range, the cells the range refers to, or the. To delete any subset of range names, first select the range names you want to delete. If the. If you want to compute total sales in Alabama, Alaska, Arizona, and. You could also point to cells B6, B7,. The latter formula is, of. As another illustration of how to use range names, look at the file Historicalinvest. Bills, and the range D8:D89 T. Now you no longer need to remember where your.
After entering the closing. I would be remiss if I did not mention the exciting AutoComplete capabilities of Excel What is the difference between a name with workbook scope and one with worksheet. The file Sheetnames. When you create names. Name box to assign the name sales to the cell range E4:E6 in Sheet3, and these cells contain.
In any worksheet, if you now enter. Then you move to Sheet2, go to the Name. Manager, and give the name jam to cells E4:E6, and define the scope of this name as Sheet2.
Because those cells contain 4, 5, and 6, you obtain In Sheet2. The unit cost and fixed cost are entered in cells F5 and F6, respectively, and profit is computed. I used Formulas, Create From Selection, and. You would like these range names to show up in the cell F4 and cell F7. Now go to the Defined Names group on the Formulas tab, click. Highlight the names you want to apply,. By the way, if you want the range names to apply to the entire worksheet, simply select the.
How can I paste a list of all range names and the cells they represent into my worksheet? Figure A list of range names and the cells each corresponds to will be pasted into. The file Last year. As shown in Figure , we want. Because we are in cell B7, Excel interprets this range name to always refer to the cell above.
Of course, this would not work if the cell reference B6 contains any dollar. For each day of the week we are given the hourly wage and hours worked. As shown in Figure see the file Namedrows. You can simply select row 12 by clicking on the 12 and use the Name box to enter the name. Then select row 13 and use the Name box to enter the name hours.
If you now enter. Because Excel has over 16, columns,. If you try and. Probably your best alternative is to. The file Stock. Name the ranges containing the monthly returns for each stock and compute the. Given the latitude and longitude of any two cities, the file Citydistances.
Define range names for the latitude and longitude. The file Sharedata. Create a range name that averages the last five years of sales data. Assume annual sales. In a horizontal lookup, the operation. False and an exact match to the lookup value is found in the first column of the table. To see an example of how to write a formula that computes the tax rate for any income level,.
FigURE Using a lookup function to compute a tax rate. The numbers in the first column of the table. I began by entering the relevant information tax rates and break points in cell range D6:E9. To illustrate how the lookup function works, I entered some incomes.
Note that because the column index in the formula is 2, the. Often, the first column of a table range does not consist of numbers in ascending order. In the file Lookup. How do you write a formula that takes a product ID. If you enter the. More specifically,. See the file Datelookup. The file Hr. Write a. Write another. The distance between any two U. The file Citydata. Create a. In the file Pinevalley. How do I write a function that returns the. I have a list of distances between U.
The file Index. The range. Suppose that you want to enter the distance between Boston and Denver in a cell. The results show that Boston and Denver are 1, miles apart. Similarly, to find the much longer distance between Seattle and Miami, you would use the. The formula. D22 computes the distance between Phoenix and Los Angeles, and so on.
The Seahawks will. Is there a way I can write a formula that references the entire column containing the distances. If you set. If you set the column. You could enter either of the following. The first formula totals the numbers in the eighth row row 17 of the Distances array; the.
Jerry Jones and the Dallas Cowboys are embarking on a road trip that takes them to. Suppose you have a worksheet with five thous and rows containing five thous and names. The file Matchex. In cell G12, the formula. Excel searches for the first text string in the range B4:B11 that begins with Pho.
If the lookup range is contained in a single row, Excel returns the relative position of the first. The file Productlookup. In C10, I use the formula. The file Baseball. The data is not sorted by salary, and we want to write a formula.
Player number has the highest salary. Finally, in. To find the name of the player with the fifth-highest salary, you need a function that yields. Proceeding as before, we find that Derek Jeter. The file Payback. We assume that in Year 1, the project required a cash outflow. We expect. How many years will pass before the project pays. In high-tech industries, the payback period is often used to rank investments.
This calculation gives you the payback. I gave the cells in B1:B3 the range names listed in A1:A3. To compute the Year 0 cumulative cash flow, I used the formula B5 in cell C5. For later years,. To implement this relationship, simply. This calculation always gives. For example, if the last row in C5:C20 that contains a value less than.
Because our first year is Year 0, the payback occurs. If any cash flows after Year 0 are negative, this method fails because the range. The file Matchthemax. The file Buslist. Write a formula that, for any arrival time after. East, North, and South regional sales. How can I extract East, North, and South sales to. For example, in sales data you download, dates and sales amounts might. Microsoft Excel text functions. The file Reggie.
The function LEFT text,k returns the first k characters in a text string. For example, cell C3. The function MID text,k,m begins at character k of a text string and returns the next m. The TRIM function also removes. The function LEN text returns the number of characters in a text string including spaces. The function FIND text to find,actual text,k returns the location at or after character k of. FIND is case sensitive. The uppercase. R is ignored because FIND is case sensitive.
The REPT function allows you to repeat a text string a specified number of times. The syntax. This formula yields Rennie. You can identify the value 31 in cell A15 as text because it is left. Similarly, you can identify the value 31 in cell B15 as a number because it is right. I have a worksheet in which each cell contains a product description, a product ID, and a.
How can I put all the product descriptions in column A, all the product IDs. In this example, the product ID is always defined by the first 12 characters, and the price is. The solution, contained in the file Lenora. To see this, put the cursor in cell A4 and press F2 to. If you move to the end of the cell, you will see two blank spaces. The results of. To capture the product ID, you need to extract the 12 leftmost characters from column B.
This formula extracts the 12 leftmost. To extract the product price, we know that the price occupies the last six digits of each cell,. I copied from cell D4 to. Extracting the product description is much trickier.
By examining the data, you can see that if. Copying from E4 to E5:E12 the formula. This formula MID for Middle begins with the thirteenth character and. This subtraction leaves. Can you put these values together to recover our.
0コメント