Data Table from Spreadsheet Application

HydroGeo

New Member
Joined
Nov 14, 2013
Messages
7
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0
The only formula you need in the data table is =myResultsSheet!myResultsCell

It RETRIEVES the result, not CALCULATES it.
 
Upvote 0
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.


 
Upvote 0
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.
 
Upvote 0
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 :LOL:).
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top