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.
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.
