Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ENGR 102: Natural infrastructure for climate resilience

Design Studio 8: Excel Skills #3: Functions and formulas

Objective:  1) Practice using functions and formulas; 2) gain exposure to sensitivity analyses.

1. Planning for ENGR 103 (15 minutes)

Next term, you will likely take ENGR 103, the next coupe in the first-year Engineering+ series. Your schedule and other factors can prevent registration for a specific section. All sections of ENGR 103 are designed to give you a foundational knowledge of computational tools that you can use throughout your engineering education and beyond. Keep a copy of this table to use when you register for Spring 2022 course, in case your schedule allows you some choice. The main goals for this activity are to:

a.  Navigate to https://engineering.oregonstate.edu/current-students/engineering-plus

b. Click on ENGR 103.

c. Review all the options for ENGR 103 next term. Note the programming language being planned and the relevant majors. If you are in EECS, C++ is probably the most relevant programming language for you. If you are in the other engineering disciplines, Python is probably the best choice for you.

d. Read the descriptions of the listed courses and fill out the table below. Write down your top four choices.

e. Rank your preferences in case your schedule and capacity require you to make choices.

2. Review of spreadsheet model for homework assignment (15 minutes)

· Review problem statement

· Review relationship between HRT, area, and flow rate

· In Temp Credit Calculator

o Review ReadMe tab

o Under Wetland Systems, review:

§ Instructions for Screening Level Analysis; discuss correlation equation and the importance of HRT

§ Demonstrate how edits to flow rate and wetland area impact Temperature Reduction (delta T, row 24) and Thermal Credits Generated (row 25 and bar plot).

§ Discuss how design variables can be adjusted. Flow rate can be adjusted monthly, but area cannot.

3. Trial and error. (10 minutes) Work independently on solving the design problem for the wetland area and flow rate. Take 5 minutes to write out your approach. If you were to design the wetland for the month in which it is most difficult to meet your objective, where would you start? Then, once you have your area set, modify the diverted flow rate to meet your thermal credit objective.

4. Functions vs. Formulas (10 minutes)

a. Want to calculate something in Excel (or Python, Matlab, etc.)? Functions and formulas are two of your options. Each should have a purpose

b. What is a formula?

i. An equation that you create in Excel

c. What is a function?

i. Predefined equation in Excel with a given name and structure

ii. Technically, a function is a formula, but it is provided by Excel rather than written by the user.

iii. Functions are located under the Formula tab in Excel. Click the cell you want to add your function to, click the Insert Function button, search for Convert, then follow the prompts.

Copy the table below into Excel and convert the following values using a formula and a function. You may need to look up the formula for the conversion online.

5. Sensitivity analysis (20 minutes)

a. What is a sensitivity analysis?

i. A workflow used to evaluate which input variables are most influential on the output variables 

b. Why do we use them?

i. Which design variable can most easily drive the desired outcome?

ii. Which variable would you target to reduce uncertainty on?

c. How do you perform a sensitivity analysis?

i. See the problem statement for details about this assignment!

ii. Independently vary each input variables (from original value) in a range of +/-10%, +/-25%, and +/-50%.

iii. Plot % change in each input variables (x-axis) against % change in the two output variables (y-axis).

iv. Compare slopes. Steeper slopes = greater sensitivity.

d. Example: What factors drive Evapotranspiration (ET)?

i. Source: Gavilan et al. (2014) Sensitivity Analysis of a Penman–Monteith Type Equation to Estimate Reference Evapotranspiration in Southern Spain

ii. From the Penmann Montieth ET equation alone, you don’t have enough information to say which variable is driving ET:

iii. By plotting the change in an input variable (x axis) vs the amount of change it produces in the output variable, you can see which variables are most important in determining the outputs. Based on the slopes of the lines in the plot below, which variable is most influential on ET for this site? (Note: AMJ in the title refers to April May June. Also, both axes are normalized, similar to the percent change function you will use later).

i. Demonstrate your interpretation by completing the table below. How much Bias Error is produced with a Systematic Error Ratio from 0 to 1? Use a line to read the Bias Error for each input variable at a Systematic Error Ratio of 1.  The value with the biggest change in the output variable for the same change in the input variable is considered the most sensitive, and it should be the same as your interpretation based on the slope of the line.

e. Practice using functions and formulas to conduct a sensitivity analysis for the temperature calculator. The objective is to identify the variable that has the greatest control on the amount of thermal credits generate.

i. Follow the directions in the problem statement to calculate

ii. Use the SUM function within Excel in Column N.

iii. Create a formula in column O to calculate percent change.

iv. The plot will be automatically populated with your data. Interpret the slope of the lines to identify which input variable (flow diverted or wetland area) is most influential.

v. Write your memo and be sure to follow the grading rubric.

Deliverable: Submit on Canvas (Individual submissions). Submit 1) your Excel file of unit conversions; and 2) Excel file for the wetlands model and engineering memo directly via Canvas.