×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Creating a table of results
4

Creating a table of results

Creating a table of results

(OP)
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?

RE: Creating a table of results

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:

CODE

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Creating a table of results

(OP)
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???

RE: Creating a table of results

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.

RE: Creating a table of results

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

RE: Creating a table of results

Hi there:

How about using SQL -- ORACLE / MS Access?

Thanks,

G. Feric, PE
http://engware.i-dentity.com

RE: Creating a table of results

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

RE: Creating a table of results

(OP)
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?

RE: Creating a table of results

2
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:

Quote:

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.

RE: Creating a table of results

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.

RE: Creating a table of results

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.

RE: Creating a table of results

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

RE: Creating a table of results

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

Cheers,
Joerd

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

RE: Creating a table of results

(OP)
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.

RE: Creating a table of results

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources