Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Data Table
I'm using Data Table to make sensitivity tables for input values. However,
when I use a formula (Y + 0.001) in the input columm the results are not calculated correctly. When I manually enter the input column, the results are calculated correctly. Why does Excel data tables not allow formulas in the input column? Example ************************************************** ******** Data Table with formula Margin input column Yearly Yearly Coverage Margin Revenue Savings Ratio "271,998,000" "87,849,698" 2.04 0.025 "271,998,000" "87,849,698" 2.04 0.025+.001 "282,877,920" "99,084,398" 2.13 0.026+.001 "304,637,760" "121,553,798" 2.29 0.027+.001 "337,277,520" "155,257,898" 2.55 0.028+.001 "380,797,200" "200,196,698" 2.89 0.029+.001 "435,196,800" "256,370,198" 3.31 Data Table with manually entered Margin input column Yearly Yearly Coverage Margin Revenue Savings Ratio "280,867,500" "87,849,698" 2.04 0.025 "280,867,500" "87,849,698" 2.04 0.026 "292,102,200" "99,084,398" 2.13 0.027 "303,336,900" "110,319,098" 2.21 0.028 "314,571,600" "121,553,798" 2.29 0.029 "325,806,300" "132,788,498" 2.38 0.03 "337,041,000" "144,023,198" 2.46 -- Donna L Bruce Sr Technical Writer Elster Electricity, LLC Raleigh NC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Data Table
<Why does Excel data tables not allow formulas in the input column?
Hi Donna, That's the way it is! But you can easily work around this limitation; the only variables you need (instead of the rows and columns you have now) are the record numbers of a table. I include a (somewhat long) article I wrote, edited by Mike fay, that explains this technique in detail. -- Kind regards, Niek Otten Microsoft MVP - Excel ===========Article by Excel MVP Niek Otten===================== Easily extend a complex Excel model for one set of input/outputs, to many sets of input/output Sometimes you want to apply an existing worksheet calculation to many sets of data rather than just the one case for which it was designed. For example, you developed a worksheet which calculates your pension. It accepts some twenty input variables and generates five output variables. Then your boss demands that you apply that one-time worksheet to 500 employees. (Holds up fist... boss!?!) Of course you could write some VBA code to do this. But many people hesitate to use VBA and often they are not even allowed to, on their work PC. Fortunately, there is a solution that does not require VBA. Excel has a feature for doing this, the /Data /Table command. But it's not well documented. Each time I was asked to do something complex using a Data Table, it took me a while to remember how to do it. So I developed this recipe. It may seem like a lot of steps, but it's actually very straightforward and can be done in minutes. Step 1. Make a copy of your original workbook, and use that to work with in the following steps. (Always make a backup before attempting anything big or new.) Step 2. Just an informational step. So that you can keep things clear, we will be dealing with three pages, called: Source WS (worksheet): The big table of input data you want to work with, such as the 500 employees' data. Aside from making it row oriented in the next step, this will not be edited/affected in any way. Model WS: Your existing worksheet which calculates one set of inputs at a time. This will only have its input cells changed, in Step 6. Data Table WS: A new worksheet with the Data Table, including its results. This is where almost all your editing will be done. Step 3. Import your source data into an Excel worksheet (of course). This is the "Source WS". All the data (input variables) for each Source WS record must be on one row (line). So work your data into a one-row format, if it covers multiple rows per input situation. If you have extra columns of data, they don't matter; just leave them. All that's needed is that each row of input data has all its data on one row. Input columns do not have to be next to each other. Step 4. Now on to the good stuff. In your Source WS, define Names (Insert /Name /Define) for the columns you need as input. If the columns have a header, do not include the header in the name. So the first line of the named area should be the first line of your variable data. Step 5. Insert a new sheet into your workbook. This is your "Data Table WS". Across row 1, starting in column B, fill the column headers (text) with the input column headers as defined in step 4. In A2, enter the number 1. In B2, enter the formula =INDEX(SourceColumn,A2), where SourceColumn is just an example of a Named range you created in step 4. Do this for all the input variables. Step 6. On your Model WS, make all your Model's input cells refer to the input columns of row 2 of the Data Table WS. So the first Model input field now has "=DataTable!B2" as a formula, instead of an input value, the second one "=DataTable!C2", etc. Do this for all the input fields in the Model. Step 7. Extend your Data Table WS to receive output by making new column headers in Row 1, to the right of the input columns. (Actually, they don't have to be to the right, as long as you keep things straight.) Now make the output fields in row 2 of the Data Table WS refer to the output fields of the Model WS. Just simple references like "=Model!C37" or "=Sheet17!C37". Step 8. Test it. Enter a number in A2 and check that it retrieves the correct input from the Source WS and generates the correct output, all on this one line. Step 9. Starting in A2, fill down a series of consecutive numbers (1,2,3...), for however many Source records you have. Don't use a formula for this; use the fill handle, the EditFillSeries command, fill it in by hand, use a +1 formula and convert using Copy/Paste Special/Values - anything that will make it a "hard" number, NOT a formula. Step 10. On the Data Table WS, select A2 to the last line and the last column of the table and then /Data /Table. In the dialog box, leave "Row input cell" blank. In the "Column input cell" box, enter A2. Click OK, and see your table filled with input data and computed results. Voila! How long did it all take... 7 minutes? Step 11 (optional). The results that have been output to the Data Table rely on a formula, as you can see if you move the cursor to their cells. They should not be "trusted" to not change. So, if you want to paste the results back into new output columns in the Source WS, you can use: /Copy /Edit /paste Special /Values. But if you want your new Source WS output columns to dynamically reflect the Data Table output columns, you can use e.g. "=DataTable!G32". Note that if you use Paste Special: Values, they will lose any formatting you might have had on the Data Table WS, but are now hard numbers in the Source WS. By the same token, Paste Special: Values will not change formatting in the Source WS, so your Source columns will keep the same format if you gave those columns a format, and then Paste Special: Values into the Source WS from the Data Table WS from time to time. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - Data Table
I figured out that if you use a Begin value and an Increment value and then
you can use a formula to create the column of input data for a data table getting the correct results. Originally, I was using a Cell Ref to the Column Input Cell in the data table formula and getting incorrect data. So now I have Begin = .025 [where Begin equals value of Column Input Cell] Increment = .001 271,998,000 87,849,698 2.04 Begin 271,998,000 87,849,698 2.04 Above + Increment 282,877,920 99,084,398 2.13 Above + Increment 293,757,840 110,319,098 2.21 Above + Increment 304,637,760 121,553,798 2.29 Above + Increment 315,517,680 132,788,498 2.38 Above + Increment 326,397,600 144,023,198 2.46 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Referencing data in a table to use in formulas | Excel Worksheet Functions | |||
Pivot Table - Create new file with ONLY certain data - Excel 2003 | Excel Worksheet Functions | |||
Data table is not updating correctly in excel 2003 | Excel Discussion (Misc queries) | |||
How do I save worksheet form data into a table in Excel 2003? | Excel Discussion (Misc queries) | |||
Pivot table displays data differently, Excel 2003 | Excel Worksheet Functions |