《DATA TABLES IN EXCEL .docx》由会员分享，可在线阅读，更多相关《DATA TABLES IN EXCEL .docx（9页珍藏版）》请在三一文库上搜索。
1、Data Tables in Excel 2007In Excel, a Data Table is a way to see different results by altering an input cell in your formula. As an example, were going to alert the interest rate, and see how much a 10,000 loan would cost each month. The interest rate will be our input cell. By asking Excel to alter
2、this input, we can quickly see the different monthly payments. Want to know how much wed pay back each month if the interest was 24 percent per year. But other banks may be offering better deals. So well ask Excel to calculate how much wed pay each month if the interest rate was 22 percent a year, 2
3、0 percent a year, and 18 percent a year.The formula we need is the Payment one you met in a previous section - PMT( ). Here it is again:PMT(rate, nper, pv, fv, type)We only need the first three arguments. So for us, its just this:PMT(rate, nper, pv)Rate means the interest rate. The second argument,
4、nper, is how many months youve got to pay the loan back. The third argument, pv, is how much you want to borrow. Lets make a start then. On a new spreadsheet, set up the following labels:So well put our starting interest rate in cell B3 (rate), our loan length in cell B4 (nper), and our loan amount
5、in cell B5 (pv).Enter the following in cells B3 to B5:So you need to enter 24.00% in cell B3, 60 in cell B4, and 10,000 in cell B5.Well enter our formula now. Click inside cell D2 and enter the following:=PMT(B3 / 12, B4, -B5)Cell B3 is the interest rate. But this is for the entire year. In the form
6、ula, were diving whatever is in cell B3 by 12. This will get us a monthly interest rate. B4 in the formula is the number of months, which is 60 for us. B5 has a minus sign before it. Its a minus figure because its a debt.When you press the enter key on your keyboard, Excel should give you an answer
7、of 287.68.Now that we have our function in place, we can create an Excel Data Table. First, though, we need to tell Excel about those other interest rates. It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT function. So it needs some new values to calcul
8、ate with.So enter some new values in cells C3, C4, and C5. Enter the same ones as in the image below:We have put the PMT function in cell D2 for a reason. This is one Row up, and one Column to the right of our first new interest rate of 22%. The new monthly payments are going to go in cells D3 to D5
9、. Excel needs the table setting out this way. So that Excel can work out the new totals, you have to highlight both the new values and the Function youre using.So Highlight the cells C2 to D5. Your spreadsheet should look like this:As you can see, the cells C2 to D5 are now highlighted. This include
10、s our new interest rate values in the C column, and our PMT function in cell D2. We can now create an Excel 2007 Data Table. This will work out new monthly payemnts for us. So do this: From the Excel menu bar, click on Data Locate the Data Tools panel Click on the What if Analysis item:When you clic
11、k on the What if Analysis item, youll see the following menu:Click on Data Table, and youll see this small dialogue box:In the dialogue box, there is only a Row input cell or a Column input cell. We want Excel to fill downwards, down a column. So we need the second text box on the dialogue box Colum
12、n input cell. If we were filling across in rows, we would use the Row input cell text box.The Input Cell for us is the one that contains our original interest rate. This is the cell you want Excel to substitute.So click inside the Column input cell box and enter B3:Click OK. When you do, Excel will
13、work out the new monthly payments:So if we could get an 18 percent interest rate, our monthly payments would be 253.93. If you click inside any of the cells D3 to D5, then look at the formula bar, you will see this:=TABLE(,B3)Thats Excels way of telling you that a Table has been created, based on th
14、e input cell B3Well try one more Data Table in the next part. Well try an easier formula, this timeA Second Data TableWell do one more Data Table, just so that you get the hang of things. This time, well use a more simple formula than PMT, and well use Rows instead of Columns. This is the scenario:Y
15、ou have 250 items that you want to sell on EBay. Your unique selling point is this - All items are only 5 each! Except, you feel 5 may be a bit expensive for the goods youre selling! What you want to know is how much profit youll make if you reduce your prices to 4.50, how much if you reduce to 4.00
16、, and how much for a reduction to 3.50. Assume that everything gets sold.To start creating your Table, construct a spreadsheet like the one below. Make sure that you start on a new sheet.In cell B1 is the number of items we want to sell (250). Cell B2 has the original price (5.00). And the Reduction
17、s Row has our new values. Cell B3 has a 0 because theres no reduction for 5.00. Row 4 is where our Profits will go.The formula to work out the profits is simply the Number of Items multiplied by the Price Per Item. So click inside cell B4 and enter the following formula:= B1 * B2Your spreadsheet wil
18、l then look like this:So if we manage to sell all our items at 5, well make 1,250. Were a bit dubious, though. Realistically, all our items wont sell at this price! Lets use an Excel Data Table to work out how much profit wed make at the other prices.Again, we put the answer in cell B4 for a reason.
19、 This is because when you want Excel to calculate a Data Table in Rows, the formula must be inserted one Column to the Left of your first new value, and then one Row down. Our first new value is going in cell C3. So one column to the left takes us to the B column. One row down is Row 4. So the formu
20、la goes in cell B4.Next, click inside cell B3 and highlight to cell E4. Your spreadsheet should now look like this one:Excel is going to use our formula in cell B4. It will then look at the new values on Row 3 (not counting the zero), and then insert the new totals for us. To create a Data Table the
21、n, do the following: From the Excel menu bar, click on Data Locate the Data Tools panel Click on the What if Analysis item Select Data Table from the menuJust like last time, youll get the Data Table dialogue box. The one we want now, though, is Row Input Cell. But what is the Input Cell this time?A
22、sk yourself what you are trying to work out, and what you want Excel to recalculate. You want to work out the new prices. The formula you entered was:= B1 * B2Excel is going to be changing this formula. You only need to decide if you want Excel to alter the B1 or the B2. B1 contains the number of it
23、ems; B2 contains the price of each item. Since were trying to work out the profits wed get if we change the price, we need Excel to change B2. So enter B2 for the Row Input Cell:When you click OK, Excel will work out the new profits:So setting a price of 3.50 per item, youd make 875 profit. Youd mak
24、e 1,000 at 4.00 per item, and 1,125 if you sell for 4.50.More advanced Data Tables in ExcelData Tables are a range of cells that are used for testing and analyzing outcomes on a large scale. A Data Table will show you how by changing certain values in your formulas you can affect the result of your
25、formula. Data Tables can store the results of many different scenarios for you in one table, so that you can analyze them to select which scenario is your best option. The results are then written into a table form in your Workbook in a location specified by you. Data Tables are written as array for
26、mulas , which therefore allows them to perform multiple calculations in a single location.There are two types of Data Tables, One-variable data tables and two-variable data tables.One-variable data tablesThe first thing you must do is to create a base or test model and tell your Data Table which for
27、mulas from your base model you want to test. This is easily done from inside the Data Table by placing a formula to reference the formula in the base model. Here is an example:Lets say that we wish to purchase a new tractor for work on our family farm. We need to know that if interest rates fluctuat
28、e we can still afford to pay for the tractor. So we need to know what our loan repayments will be, what our total repayments will be and how much interest we are paying.1. Open the attached workbook on the Base Model worksheet. The highlighted cells contain formulas.2. Now click on the Worksheet tab
29、 named OneVariable Table and notice that this has exactly the same data as the previous table, plus an additional area already set up for the Data Table. 3. Click in cell E3 and type in =B11, which is the Monthly Loan Repayment 4. Click in F3 and type in =B13 which is the Total Repayment. 5. Click i
30、n G3 and type in =B14 which is the Total Amount of Interest Paid. 6. Highlight the range of the table D3:G9, click DataTable 7. Leave the first box, Row input cell, blank. Nothing is required for a one-variable table8. Click in Column input cell, click on the collapse dialog button and select cell B
31、5 which contains the original interest rate of 8.97% 9. Collapse back through to your Table dialog box and select OK. You should now see the results of the calculations given the values entered in D4:D9 that would appear in cells B11, B13, and B14 of your base model pasted into the table. Two-variab
32、le Data TablesYou can use a two-variable Data Table to gauge the effect on one formula by changing the value of two input cells within the one table. With this type of table, you can nominate two series of data that can be placed back into the original model into two different input cells. Using the
33、 example above, this means that you could nominate a series of interest rates to place in the original interest rate cell (B5) and a series of loan terms to place in the Term of Loan cell (B7). When creating a two-variable table, one series is entered into the first column of the table and the other
34、 into the first row of the table. As when creating a one-variable table, the formula reference that we want to test needs to be placed into the blank cell at the top of the first column of the table. The attached workbook shows an example of a Two-variable Data Table.RulesA couple of rules for Data
35、Tables:1. Set up a base model 2. Do not change the values in the base model 3. It is a good idea to document the area around your data table, so you and other users can tell what it is you are analysing. 4. You can use Data Tables to change up to two variables only 5. You can create as many one-variable or two-variable Data Tables as you like in a Workbook. Hopefully, Data Tables werent too difficult! But they are a useful tool when you want to analyze values that can change.