Creating Computational Models with Excel

(AKA Excelettes)

Purpose

Learn how to use Excel to create computational modeling tools that incorporate easy-to-change variables so as to quickly illustrate their impact on a system’s behavior or output.

Creating an Excel Computational Model / The Home Faucet Audit Example
  1. Select an activity or experiment to model.
/
  1. The Home Faucet Audit

  1. Analyze the experimental scenario and process:
  2. What is the question that your experiment will answer?
  3. What is the outcome or result that you are watching?
  4. What factors (variables) affect the results?
  5. Which of these factors will you keep constant throughout the experiment?
  6. Which of these factors will you intentionally vary to see how they affect the results?
  7. Which factors will you ignore?
/
  1. The Home Faucet Audit Scenario
  1. How much water do we use each day via the bathroom faucet?
  2. Amount of water used (daily-usage)
  3. Factors that affect how much we use:
  4. How often we turn on the faucet (frequency)
  5. How long the faucet is on with each use (duration)
  6. Why we turn on the faucet (purpose)
  7. How much water flows from the faucet each minute (flow-rate)
  8. Constants: flow rate
  9. Independent variables: frequency, duration
  10. Ignore: purpose

  1. Write the equation that relates the variables to the results.
/
  1. Open Excel and display the Developer Tab (Win) or the Forms Toolbar (Mac).
  2. Windows Office 2010: File -> Options -> Customize Ribbon. Choose commands from: Main Tab. Click to highlight Developer (do not expand). Click Add. Click OK.

  1. Windows Office 2007: Microsoft Office -> Excel Options -> Popular -> Show Developer Tab in Ribbon.
  2. Mac: Views -> Toolbars -> Forms.

  1. Format a spreadsheet:
  2. Enter atitle into Row 1 that is descriptive of the experiment.
Tip: once all of your column headings are in place select all of the Row 1 cells across the columns in use and click the icon to merge the Row 1 cells and center your title.
  1. Enter column headings into Row 2, assigning one column to each factor/variable identified and one to the result.
Tip: if your column headings are long, click on the icon before starting to type. This will force your heading to stay within the width of the column. You won’t need to use multiple rows of cells for your multi-line headings.
Tip: adjust column widths, font sizes and types, colors etc. to make your spreadsheet easy to read. / 5.Home Faucet Audit spreadsheet set up w/ the experiment’s question as the title and descriptions of the variables used as column headings.

  1. Set up data entry:
  2. Enter the equation from above into Row 3 of the results column.
Tip: Use the Insert Function feature, , to “build” your equation.
  1. Leave the row under the column heading blank. Draw a value slider into the second cell undereach factor’s/variable’s heading.
Tip: for the Home Faucet Audit example, under the purpose column, merge cells A3 and A4 so that one purpose brackets two rows, one for the value and one for the slider bar.
Windows Office 2010:
  • Click on the Developer Tab
  • Click Controls -> Insert
  • Click Form Controls -> Scroll Bar
Mac:
  • On the Forms toolbar, select the Scroll Bar: Form Control. It is the 5th one down on the left hand side.
  1. Having selected scroll-bar tool, click and drag to draw your scroll barnearthe target cell.
Tip: whenever the anchors (corner boxes) are showing on the scroll bar, you can move it around to position it on your screen. Use the arrow keys on the keyboard for fine-control over the scroll bar position.
  1. Right click on the scroll bar and select “Format Control”.
  2. In the Format Control dialog box, set the Current Value = the default value of this variable; Minimum Value = lowest value this variable could have; Maximum Value = highest value this variable could have; and the Incremental Change = smallest change possible (usually the significance of this value).
Tip: All values must be whole numbers.
Tip: You do not need to change the Page Change control value.
  1. Click on the icon next to the Cell Link field, . AFormat control window will open.
  2. Click on the cell above the scroll bar / below the column heading. The cell address will appear in the Cell Link field.
  3. Hit the Enter key and click OK. The numerical value of this variable will display in that cell.
  1. Your model is complete; try it out!
/
  1. Home Water Audit example data entry set-up:
  1. Entering the equation:

  1. Adding a scroll bar for the number of times a water use/purpose happens each day:

  1. The completed model: sliders have been adjusted to enter realistic values. The values can be easily increased or decreased to see what impact changes in “brushing teeth” would have on water usage.

Summary/Analysis of the Home Faucet Audit Example

In the example, we created an Excel file to model water savings. Here is how it works.

  1. If my faucet flow rate is 3.5 gallons per minute, and I have the water running for 240 seconds (4 minutes), while I brush my teeth, I will use 14gallons of water each time I brush my teeth.
  2. Since I brush my teeth 2 times/day, this amounts to 28 gallons/day.
  3. If all 4 people in my family do the same thing, we are using 112 gals/day for this purpose.
  4. I can think of having everyone in my family reduce the amount of time they have the water running to 60 seconds by turning the water off and on during the brushing. I can test my idea by adjusting the slider(s) on the computational model and quickly see that it will result in our family usingonly 28 gals/day for teeth brushing!

This tool is a quick, easy, informative, and motivating way to evaluate the impact of input variables on the output of an experiment!