Efficient Parametric Studies with Excel

Hello, I was hoping to find an easy solution to the following problem.
Thanks in advance for any help you might be able to provide.

Say I have a single cell whose value is a function of values of two
other cells

A3 = A2 * A1

If I were to make a multiplication table with A1 on one axis and A2 on
the other, would I be able to fill it out by using the function
specified above?  Up until now, I have had to manually enter in each
values into cells A1 and A2 and then 'special paste' the value of A3
into each table entry.  This can get quite time consuming for tables
with big dimensions.  Is there an easy way to do this?  Thanks again.

-Yuto S.

0
yuto (3)
3/16/2005 3:40:09 PM
excel 39879 articles. 2 followers. Follow

5 Replies
541 Views

Similar Articles

[PageSpeed] 9

Don,

Let's say that you want a table with 8 values for A1, and eight values for
A2, for a total of 64 values.

With your "A1" values in the first row, starting in cell B1 and continuing
to I1, and your "A2" values in column A starting at cell A2 and continuing
down to A9.  In cell B2, enter the formula

= $A2*B$1

and copy that cell to B2:I9.

You could also use Excel built-in data table feature: I have pasted the help
item about data tables below.

HTH,
Bernie
MS Excel MVP

Create a two-variable data table
Two-variable data tables use only one formula with two lists of input
values. The formula must refer to two different input cells.
In a cell on the worksheet, enter the formula that refers to the two input
cells.
In the example below, where the formula's starting values are entered in
cells B3, B4, and B5, you would type the formula =PMT(B3/12,B4,-B5) into
cell C2.
Type one list of input values in the same column, below the formula.
In the example below, you would type the different interest rates into cells
C3, C4, and C5.
Type the second list in the same row, to the right of the formula.
In the example below, you would type the loan terms (in months) into cells
D2 and E2.
Select the range of cells that contains the formula and both the row and
column of values.
In the example below, you would select the range C2:E5.
On the Data menu, click Table.
In the Row input cell box, enter the reference to the input cell for the
input values in the row.
In the example below, you would type cell B4 in the Row input cell box.
In the Column input cell box, enter the reference to the input cell for the
input values in the column.
In the example below, you would type B3 in the Column input cell box.
Click OK.


"Don Karnage" <yuto@americanairport.us> wrote in message
news:1110987609.831473.159900@f14g2000cwb.googlegroups.com...
> Hello, I was hoping to find an easy solution to the following problem.
> Thanks in advance for any help you might be able to provide.
>
> Say I have a single cell whose value is a function of values of two
> other cells
>
> A3 = A2 * A1
>
> If I were to make a multiplication table with A1 on one axis and A2 on
> the other, would I be able to fill it out by using the function
> specified above?  Up until now, I have had to manually enter in each
> values into cells A1 and A2 and then 'special paste' the value of A3
> into each table entry.  This can get quite time consuming for tables
> with big dimensions.  Is there an easy way to do this?  Thanks again.
>
> -Yuto S.
>


0
Bernie
3/16/2005 4:12:30 PM
Apologies fro my lack of response, and a big thanks to such a thorough
explanation.  I didn't even know the 'table' function existed in Excel,
though it seems like a logical feature and considering it's not hidden
away in some obscure array of menus.  One problem though. . .what if I
have a complex spreadsheet that say, calculates the drag on an
airplane.  The drag may be a function of hundreds of parameters and the
routine may require me to calculate several sub-parameters along the
way.  In cases like this, it's almost impossible to be able to cram the
equation into one cell.  For example:

A1: altitude
A2: speed
A3: span of aircraft
A4: weight of aircraft
A5: geometry of wing
.....

A6 = a fraction of the total drag as a function of A1 and A2
A7 = a fraction of the total drag as a function of A3 and A4
A8 = a fraction of the total drag as a function of A5

Total drag = A6 + A7 + A8

It seems to be that the Table function wouldn't work in this case
because it requires the user to write out the full expression for drag
in a cell (ie. Total drag as a function of A1, A2, A3, A4, A5).  I've
been doing a little experimenting to see if I can coerce Excel into
doing what I want.  Thanks again for your help!

-Yuto S.

0
yuto (3)
3/21/2005 8:27:34 PM
Don  -

For example, if you have a list of altitude values in a column, and if you 
want corresponding total drag in an adjacent column, enter =A9 at the top of 
that adjacent column (where total drag is calculated in cell A9), and then 
use the Data Table command, specifying column input A1.

For examples of one-factor and two-factor data tables, in a very different 
context, but where the output formula depends on the inputs with many 
intermediate variables, see pp. 30-34 in "Decision Trees Using TreePlan," 
file treeplan.pdf, available on the "Download Free Tryout" page at 
www.treeplan.com.

-  Mike
www.mikemiddleton.com

"Don Karnage" <yuto@americanairport.us> wrote in message 
news:1111436854.642509.133750@o13g2000cwo.googlegroups.com...
> Apologies fro my lack of response, and a big thanks to such a thorough
> explanation.  I didn't even know the 'table' function existed in Excel,
> though it seems like a logical feature and considering it's not hidden
> away in some obscure array of menus.  One problem though. . .what if I
> have a complex spreadsheet that say, calculates the drag on an
> airplane.  The drag may be a function of hundreds of parameters and the
> routine may require me to calculate several sub-parameters along the
> way.  In cases like this, it's almost impossible to be able to cram the
> equation into one cell.  For example:
>
> A1: altitude
> A2: speed
> A3: span of aircraft
> A4: weight of aircraft
> A5: geometry of wing
> ....
>
> A6 = a fraction of the total drag as a function of A1 and A2
> A7 = a fraction of the total drag as a function of A3 and A4
> A8 = a fraction of the total drag as a function of A5
>
> Total drag = A6 + A7 + A8
>
> It seems to be that the Table function wouldn't work in this case
> because it requires the user to write out the full expression for drag
> in a cell (ie. Total drag as a function of A1, A2, A3, A4, A5).  I've
> been doing a little experimenting to see if I can coerce Excel into
> doing what I want.  Thanks again for your help!
>
> -Yuto S.
> 


0
middleton1 (108)
3/21/2005 8:46:49 PM
Yuto,

In your case, custom code would probably be better than trying to design a 
spreadsheet.

HTH,
Bernie
MS Excel MVP


"Don Karnage" <yuto@americanairport.us> wrote in message 
news:1111436854.642509.133750@o13g2000cwo.googlegroups.com...
> Apologies fro my lack of response, and a big thanks to such a thorough
> explanation.  I didn't even know the 'table' function existed in Excel,
> though it seems like a logical feature and considering it's not hidden
> away in some obscure array of menus.  One problem though. . .what if I
> have a complex spreadsheet that say, calculates the drag on an
> airplane.  The drag may be a function of hundreds of parameters and the
> routine may require me to calculate several sub-parameters along the
> way.  In cases like this, it's almost impossible to be able to cram the
> equation into one cell.  For example:
>
> A1: altitude
> A2: speed
> A3: span of aircraft
> A4: weight of aircraft
> A5: geometry of wing
> ....
>
> A6 = a fraction of the total drag as a function of A1 and A2
> A7 = a fraction of the total drag as a function of A3 and A4
> A8 = a fraction of the total drag as a function of A5
>
> Total drag = A6 + A7 + A8
>
> It seems to be that the Table function wouldn't work in this case
> because it requires the user to write out the full expression for drag
> in a cell (ie. Total drag as a function of A1, A2, A3, A4, A5).  I've
> been doing a little experimenting to see if I can coerce Excel into
> doing what I want.  Thanks again for your help!
>
> -Yuto S.
> 


0
Bernie
3/21/2005 9:32:02 PM
It worked!  Thanks to both of you for your help.  Bernie, you're
probably right that it's easier to do higher level analysis with Matlab
or something, but an Excel spreadsheet is much easier to distribute to
others and have them understand what's going on.

Take care,
Yuto S.

0
yuto (3)
3/22/2005 4:10:13 PM
Reply:

Similar Artilces:

have to read data from Excel
Hi, I want to read and write the data from the excel file. I dont know how to do this. Any pointer will be helpful Thanks in advance Gaurav "Gaurav" <gauravgoyal121@gmail.com> ha scritto nel messaggio news:1185881881.293085.303740@d30g2000prg.googlegroups.com... > I want to read and write the data from the excel file. > I dont know how to do this. Any pointer will be helpful Hi, I think that you might consider using some file format different from XLS, e.g. you may use CSV (comma separated values) or XML. Excel can manage CSV and XML file formats, so you may exp...

Excell 2007????
Has anyone any insight on changes to the charting options in Excel 2007? What I have found emphasizes integrating the excel charting with word and powerpoint and fancy chart themes. But there's nothing on any new chart types, like boxplots or things like scatterplot data labels. I know we can download the beta, but I'm afraid of what it might do to my existing version of office.. Gary: Here's an article by Stephen Few on Excel 12. http://www.b-eye-network.com/view/2818 Stephen's point is that Microsoft has missed a big opportunity. Kelly ...koday@processtrends.com ...

Refresh Imported Data
This probably sounds dumb - I have a spreadsheet that I have imported data from an existing dsn/odbc, I set the properties to refresh every 24 hours (1220 minutes), and on open. The file is going to be used for an orgchart in another program that imports the data from excel. I am testing that automation also, it has tasks that refresh the data from the excel file. What I don't know is if the excel spreadsheet has to be open for the refresh to occur? That is not a problem, so far, but not sure yet if I can refresh the orgchart file if the excel file is open. The files will all be...

how do i get a 'save' sound in excel?
How do i get the excel programme to give me a confirmation sound when i have saved? (cntrl+s) so i dont need to look up to see if its saved the data. Depending on your version of office: 2k, 2002, 2003: http://www.microsoft.com/downloads/details.aspx?FamilyID=C8BB981D-2A23-4D1A-8310-457C733BE290&displaylang=EN (one line in your browser) or http://snipurl.com/2z7v 97: http://www.microsoft.com/downloads/details.aspx?FamilyID=9293ba43-2810-482c-9168-8122a9d76ecd&DisplayLang=en or http://snipurl.com/eqe9 Frannie wrote: > > How do i get the excel programme to give me a confirm...

Is there any way to use gridlines in the Excel Calendar Templates?
...

Excel vba autofilter code
I have some code to filter a result set to a list of unique names - is there something I can add that will also remove 'blank' entries ? Range("AP1:AP" & LastRowNewData).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Range("A44"), _ Unique:=True Thanks ...

how to publish excel workbooks
Hello My problem is simple, i need to publish to my clients a catalog of electrical material that has over 60.000 entries, and with links between more then one sheet. Problem is i dont want users to copy/paste, or change the data, only can view it. Of course i wont use Excel built in protection since it offers no security at all, the same problem applies to VBA coding, that can be easily forced in. Solution was to export this to html, and from html to flash, but i think it will be very slow. Basically the ideal would be to export he whole workbook intact to flash, that would be swell. Ok t...

Missing function in Excel 2003 #2
I have a user that is complaining about a missing function in Excel 2003. He calls it a "Coalition matrix", I have never heard of it before. He used to have it in Excel 2000. Any ideas? Hi never heard about this. Maybe he has to install the Analysis Toolpak Addin ('tools - addins'). "Galldrian" wrote: > I have a user that is complaining about a missing function in Excel > 2003. He calls it a "Coalition matrix", I have never heard of it > before. He used to have it in Excel 2000. Any ideas? > Never heard of a coalition matrix. The Analysi...

Code for making Excel beep
Hi I'm not sure how to alter the code below to achieve what i want. I have the following code that i run at the end of a macro,which give an audible beep if conditions are met. If Range("B2").Value > Range("C5").Value And Range("C5") > "" Then xcount = 1 For xcount = 1 To 5 Beep newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 1 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime Next End If End Sub Below is part of a formula that outlines the new conditions that nee to be met. I...

Merged cells when exporting reports into excel files
The excel files exported by custom reports we have made using reporting services, have a lot of merged cells. Do you have any idea why does this happen ??? Hi George, We have experienced this before and would like to share our findings Check your report to see if you have any labels added to the report... Generally Report Headings that are wide enough to extend the width of the columns below... Fix is to get to the width of the label to be equal to the width of the entire column being merged. So say you find column D and E merged. Extend the report header label to have a width wide e...

Copy filename from Excel 2010 Recent Workbooks to Clipboard
I often reuse recently opened workbooks in SQL Server Management Studio/SSIS. Please add the ability to copy a workbook name to the clipboard in the "Recent Workbooks" right click. Can this be included in a Windows Update? PS Love Excel 2010: great improvement and work! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web...

How do I convert 999999 to 999-999 in an excel document?
I have copyied a large number of parcel numbers into excel (maybe 3,000) and I need to change the format in order to use them in another application. For instance: 999999 needs to become 999-999 OR 99999 needs to become 099999. How can this be done without changing each entry manually. Thank you. Hi Chris if the numbers could be either 6 characters long or something less than 6 at one time (ie you don't do a batch of 6 digit numbers and then a batch of 5 and then a batch of 4) then one way is to use a helper column (ie another column adjacent to your numbers) with the following ...

drop down list in excel #2
is it possible to make a drop down list box bigger? Is it possible to make the drop down list box always start at the first entry in the lst rather then the last one selected? All help appreciated Andy Andy, if you select ONLY the populated cells in your list behind your dropdown, it will start at the top of the list. If you also select a bunch of empty cells, it won't start at the top. Period. ******************* ~Anne Troy www.OfficeArticles.com "Andy B" <Andy B@discussions.microsoft.com> wrote in message news:01B3F9D8-7EBD-4623-90FA-599975BFBE6D@microsoft.com... ...

How to import MS Excel Spreadsheets into MS Money 2004 Deluxe
Is there a mechanism to import a MS Excel Spreadsheet into MS Money 2004? A bank will only provide the transactions in Excel spreadsheets. Any help will be appreciated. Thanks, Varun See http://umpmfaq.info/faqdb.php?q=8. "Varun Gupta" <varunsgupta@yahoo.com> wrote in message news:17d0a01c449b6$376fdd10$a301280a@phx.gbl... > Is there a mechanism to import a MS Excel Spreadsheet into > MS Money 2004? > A bank will only provide the transactions in Excel > spreadsheets. > Any help will be appreciated. ...

como relaciono campo de nombre excel con contactos outlook
...

Need Excel equivalent of Lotus CTRL-SHIFT-PG UP/DWN #5
Ohhhh let me try that...thanks -- vpr8 ----------------------------------------------------------------------- vpr80's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1578 View this thread: http://www.excelforum.com/showthread.php?threadid=27340 ...

money to excel
Is there a way to get a downloaded money file from a bank which is in QIF or OFX from money into an excel file? ...

Cannot open excel program
I can not open my excel program. I get the error message a invalid page fault excel.exe.0187:30026d69. I can open the program in safe mode. Any ideas on how to correct this? Jan Have a look at this article: http://support.microsoft.com/default.aspx?scid=kb;[LN];280504 I'd look first at the "Excel Toolbar File" section. -- Jim Rech Excel MVP ...

Help! How to add many excel charts into PowerPoint at once?
I am working on a project in which we need to be able to view 180+ charts easily. I wanted to insert them into PowerPoint easily, but am having trouble finding a way to do that. Thanks! Joules, I do much the same and here is how get around it... (1) My charts are all created within a macro and their size and positions are designated on the worksheet (I choose to fix them to a particular cell location always) (2) After the macro creates all n-number of charts (you can play with the sizes so the transfer to ppt still leaves them in a readable state) I select the cells and hold [shift] while...

Excel deleting some numbers from an excel document
I have a spread sheet that has (example) numbers in a cell 45% (5/11) in a cell or 60% (60/100) in a cell. I have hundreds of cells each with different series of numbers all with a 1-2 or 3 diget % and two numbers in (). I want to delete the number in parenthesis and the parenthesis. I can do it cell by cell but I figure that there should be some way to reformat the cells to do it in mass =LEFT(A1,FIND("%",A1)) -- Kind regards, Niek Otten Microsoft MVP - Excel "Seth" <Seth@discussions.microsoft.com> wrote in message news:B1383B72-E904-457B-BB5F-EC0573D1...

Events in Excel for executing functions
Is there any special event that excel fires when a specific calculation is completed in a CELL. For example in Cell A5 I have =FuncLookAtAllDBRequest("SomeStringID") When this function completes I need to know whether I can call some macro to send me an email using VBA. Are there any events that tell BeforeFunctionCalled(".....") or AfterFunctionCalled(".....") etc.... Is this already there is Excel or are there any VBA code sample that will do it. I have several functions been called within a sheet they do scientific calculation and I need to notify the needful...

colored text prints black on embedded excel charts
Just upgraded to XP Pro machines from NT and are still using office 2k. For some reason (possibly a memory problem?) when I drop in a label or text box and color the font to something other than black it may or may not print in color and after second send to print will only print in black. It appears fine on the screen though. Any known workarounds for this? Thanks~ Aminna Aminna - You may need updated drivers so the printers work with XP. Check the manufacturer's web site. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions ...

What is an addin (in Excel) and how can we create one?
Hi, I want to know what an excel addin is and how to create an excel addin (not the method Save as .xla) for say, to check the occurances of an item. In Col A, several items are listed like item1, item2, item3 etc upto item10. Some items repeat more than once. Can an addin be created to check the no. of occurances of AN item in this list? If so how> Thank You Hi Shajueasow, See: http://www.fontstuff.com/vba/vbatut03.htm for a tutorial. --- Regards, Norman "shajueasow" <shajueasow@discussions.microsoft.com> wrote in message news:9DC9E994-13FE-4FFA-957E-10...

Excel 98 Question
Not sure if this is an issue on our LAN or if this is an Excel 98 bug - I have got some spreadsheets that after I update the source data for charts (on manual calculation) the actual chart will not reflect the change. If I select the series and hit enter on the formula bar, the chart updates. Any suggestions? thanks -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread.php?threadid=319019 I don't use Excel 98...

Excel not recognizing files
Email attachments are not being recognized by Excel. I have to save them to a directory instead of clicking on them. NOt a giant deal but I have selected Excel to open these file types. USing XP pro and Office2000. Any help greatly appreciated. My earlier message didn't get posted unless it is an accident so if this is a duplicate I apologise for the redundnacy. Rick Sometimes one of these help when double clicking on a file in windows explorer. Maybe it'll work inside your email program, too. Close Excel and Windows Start Button|Run excel /unregserver then Windows Start Button|Ru...