Creating a table of results 4

[SteveGregory]

I have a series of input variables and several formulas leading to a final formula with the desired result.

What is the easiest way to make a table using a single input variable and the final resulting value? I thought about lining up all of the input and the equations in a single row, copying the row down and then hiding all of the columns except two. I also thought about copying and pasting the values one at a time into a table. Too much work!

Any ideas?

 

[joerd]

Your first method is the most straightforward and easy to understand. But if your spreadsheet becomes complex, it might be difficult to align all the equations/formulas in one row. Then, what I usually end up doing is write a little bit of VB code to accomplish your second suggestion. It basically comes down to:

Sub GenerateTable()
Dim w As Worksheet
Dim InputCell As Range, ResultCell As Range
Dim i As Integer

Set w = Worksheets("Sheet1")
Set InputCell = w.Range("A1")
Set ResultCell = w.Range("C1")

For i = 10 To 15
    InputCell.Value = w.Cells(i, 1).Value
    w.Calculate 'not required if calculation is automatic
    w.Cells(i, 2).Value = ResultCell.Value
Next i

End Sub

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[SteveGregory]

Thanks Joerd,

I have never tried VB before. I put a list of input values in the range A1-A20 on Sheet2 and I want the VB to put the results in B1-B20 on Sheet2. My input cell is B10 on Sheet1 and my result formula cell is B16 on Sheet1. I don't have a clue???

 

[MintJulep]

I've never used it, but I'm pretty sure that the Scenario manager (accessible from the tools menu) does what you want to do.

 

[SteveGregory]

Thanks MintJulep,
I already tried using The Scenario manager and I don't think it applies.

 

[Feric]

Hi there:

How about using SQL -- ORACLE / MS Access?

Thanks,

G. Feric, PE

http://engware.i-dentity.com

 

[SteveGregory]

I'm looking for a quick and dirty solution. I'm not sure what SQL is.

 

[IRstuff]

I'm confused. If you had even done a copy onto a clean sheet, you'd have been done by now.

TTFN

FAQ731-376

 

[SteveGregory]

At my client's request, I expanded my list to 47 data sets and I ended up changing the input 47 times, then copying and pasting 47 times 2 values.

I would like to understand how to use the VB code that Joerd posted for next time. It looks simple. However, I have never tried using VB before. Maybe you can teach an old dog new tricks.

Anyone?

 

[IRstuff]

I'm still confused. Did you want all 47 sets of data displayed on the same sheet?

TTFN

FAQ731-376

 

[jghrist]

Sounds like an application for a Data Table to me. Put the various input values in a column. Put a formula at the top of the column to the right of the input values. The formula needs to depend on the value in an "input cell". This could be a reference to the result of several formulas. Data Table fills in the column below the formula with results of copying the values of the input column into the input cell.

The Web-based help for Data Tables in the latest version of Excel is pretty useless. The following is from an earlier version:

Fill in a one-variable, column-oriented data table

1 In a single column, type the list of values you want to substitute in the input cell on your worksheet.
2 In the row above the first value and one cell to the right of the column of values, type the formula for the input cell.
3 In the same row, type additional formulas to the right of the first formula.
4 Select the range of cells that contains the formulas and values you want to substitute.
5 On the Data menu, click Table.
6 In the Column Input Cell box, enter the cell reference for the input cell.

 

[Denial]

I agree with jghrist that the Data>Table feature is almost certainly exactly what SteveGregory is looking for. It is a very useful feature, which is (most unfortunately) inadequately publicised and badly explained. It has the further advantage that everything remains "hot-wired", and changes elsewhere in the spreadshee will be automatically reflected in the Table.

 

[MintJulep]

If I understand your question correctly, the scenario manager and its summary report does EXACTLY what you are asking to do.

I've just been playing with it, it's pretty cool.

Take a few minutes to play around with it.

 

[SteveGregory]

Thanks Everyone,
The Data Table is exactly what I needed!

 

[joerd]

So what makes a Data Table different from just copying the formulas down?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[SteveGregory]

In my case, I was changing only one input variable of the 5 input values in my worksheet. There were 8 separate formula cells. I was creating a table with 47 input values and 47 calculated values.

The Data Table can be created anywhere in the worksheet after all of your formulas and input cells are already in place and working. The table only requires a single formula placed at the top of the "results column" and a column of data for your input. Highlight the table and select Data/Table, identify the related input cell used by your formulas and presto, the results appear.

These results are live and will change if you change the numbers in the input column or any other input cell related to your formulas. Also, you can use this table to produce a graph of your results.

 

[Denial]

SteveGregory, Have you ever thought about a job in Sales?