Data Table from Spreadsheet Application

[HydroGeo]

I am using a couple of spreadsheet applications using Excel. The applications require inputs for certain parameters and then output predicted values. I want to vary the inputs and create a data table showing results for each scenario. Can I do this using easily a combination of macros and “what if” scenarios?

For example, one application predicts the concentration of a volatile chemical in the air inside a building. Inputs include various parameters, such as the chemical name, concentration of the chemical in the soil, soil type, soil moisture content, exposure duration, building dimensions, etc. Some parameters can populate automatically based on other inputs. For example, a default soil moisture number is based on the soil type. Let’s say I keep all other inputs the same and just want to run the application for several chemicals. I have a table with the chemical names and concentrations. I want run a routine of some sort that will input each chemical name and concentration into the application, calculate the results, and then tabulate the results for all the chemicals.

I tried simply using a data table and copying the cell that shows the results, but it gives me the same value for all chemicals. Obviously, this approach does not substitute the chemical name and concentration for each scenario. I can’t copy the actual overall equation that provides the result because it is complex and based on numerous cross-linked equations within the application.

Just running the application for a list of chemicals and their concentrations is the minimum I would like to achieve. It’s tedious to go back and enter each chemical individually. Even better would be the option to vary other input parameters, too. For example, given a certain concentration for benzene at the source, I would like to know the results for various scenarios using a range of soil types.

I hope I explained this properly. Any help would be appreciated. I’m a scientist and not a programmer. So, I was hoping to do this using built in macros and Excel capabilities rather than writing a script. I don’t know VBA or coding, though I am thinking I may have to learn them to get more out of Excel and/or Access.

 

[shg]

A data table support one or two inputs, e.g., a 2D data table would allow a row input for chemical and a column input for concentration. More input variables would require VBA.

 

[HydroGeo]

I tried using a data table, but it didn't work. The application consists of four worksheets. The first worksheet is the data entry. The second is the "intercalc" -- the page where calculations are conducted. A third is the lookup table. And the fourth provides the results. So, I would set-up a table on a fifth sheet with the chemical names and concentrations. Then I want the routine to, one a time, input each chemical name and concentration, calculate the result, pull the result and tabulate in a data table.

 

[shg]

IIRC, the row and column input cells for a data table have to be on the same sheet where data table appears. So the data table should be on the input sheet, and the body of the table would need a formula to retrieve the result from wherever it gets calculated.

 

[HydroGeo]

That's the issue. I can't post a formula in the data table. It would be impossible. The result is based on several equations and lots of relationships and formulas. I want to automatically input each chemical and then tabulate results.

Here is the data input worksheet. I highlighted the input cells in yellow for convenience. I added a data table to the worksheet per your instructions and highlighted it in light red.

I set-up the data entry worksheet so that for each chemical name (based on a CAS number), it looks up a concentration from a table. So, I just need to enter the CAS number for each chemical.



Here is the results page. I want the data table to take the two results and tabulate them for each chemical.

 

[HydroGeo]

Here is the equation for carcinogenic risk:

=IF(ISERROR(MATCH(DATENTER!E12,CAS_No,0)),"ERROR",IF(INTERCALCS!J31="NA","NA",(INTERCALCS!J31*DATENTER!H53*DATENTER!G53*INTERCALCS!I31)/(DATENTER!E53*365)))

And here is the equation for hazard quotient:

=IF(ISERROR(MATCH(DATENTER!E12,CAS_No,0)),"ERROR",IF(INTERCALCS!K31="NA","NA",(DATENTER!H53*DATENTER!G53*(1/INTERCALCS!K31)*INTERCALCS!I31*0.001)/(DATENTER!F53*365)))

The "INTERCALCS" cells in the formula refer back to other cells and formulas.

So, rather than including the formula in the data table, I was hoping for a routine to enter inputs and read outputs.

 

[shg]

The only formula you need in the data table is =myResultsSheet!myResultsCell

It RETRIEVES the result, not CALCULATES it.

 

[HydroGeo]

So, if I understand you, you are saying that if my result is in cell B3 on RESULTS sheet, I just need to use the formula =RESULTS!B13


However, that is the formula I used and I did not get results for each chemical. I got the same values for all. Using that formula does not plug in the different chemicals and concentrations on the data enter sheet.

I want to automatically plug in the "benzene" and the concentration of benzene, then retrieve result and put it in data table in column next to benzene.
Then automatically plug in the "toluene" and the concentration of toluene, then retrieve result and put it in data table n column next to toluene.
And so on through the list of chemicals.

 

[shg]

If you put the workbook on box.net and post a link, I'll look at it.

BTW, the formula should only appear once in the top left cell of the data table.

 

[circledchicken]

Hi,

As shg mentioned, data tables are normally used with one or two input variables, however you can extend them for more complex scenarios by storing your scenarios elsewhere and making one of the input variables a scenario.

If you can provide a small example with clearly labelled inputs and expected outputs, I don't mind having a look as well (although shg's solution will be better ).