04 ExcelFunctions

# 04 ExcelFunctions - CEE 3804 Spreadsheet Functions CEE 3804...

This preview shows pages 1–11. Sign up to view the full content.

CEE 3804: Spreadsheet Functions CEE 3804 Computer Applications for Civil and Environmental Engineers

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
CEE 3804 - Rojiani 2 Topics to be Covered z Using Excel’s Built-in Functions z Understanding Excel’s Error Codes z Auditing Worksheet Formulas
CEE 3804 - Rojiani 3 Function Basics O perator Sequencing and Precedence z Formula results depend on the operator sequencing and precedence: z (2+6)/2 = 4 z 2+6/2 = 5 z Excel sequence in operations: z left to right: parentheses exponential calculations multiplication and division addition and subtraction

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
CEE 3804 - Rojiani 4 Function Basics Reference Operators z Excel uses three reference operators: z Colon: cells between and including two cell references e.g. A1:A5 refers to A1, A2, A3, A4, and A5 z Comma: indicates the union of two ranges e.g. A1:A3,B4,B6:B7 refers to A1, A2, A3, B4, B6, and B7 z Space: indicates the intersection of two ranges C1:C5 B3:G3 refers to cell C3
CEE 3804 - Rojiani 5 Function Basics Creating Names and Using Text z Define range including row and column names: z Insert/Name/Create z Creates names for rows and columns z A3: Nice B3: Person C3: A3&” “&B3 gives Nice Person

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
CEE 3804 - Rojiani 6 Function Basics Array Formulas z Arrays allow: z multiple values as input to a single formula, and z produce either a single or multiple output z An array formula shared by multiple cells is more efficient in terms of memory standpoint: z Excel only stores a single formula z Drawbacks: z cannot insert, delete, or move cells within an array range
CEE 3804 - Rojiani 7 Function Basics Array Constants z Values can be entered as an array constant: z elements separated by commas z rows separated by semicolons z array surrounded by {} brackets z Example : z ={96,94,77;90,91,85;84,93,84} z depending on range defined the array or a portion of the array are displayed

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
CEE 3804 - Rojiani 8 Function Basics Natural Language Referencing z Natural Language Referencing is a feature that: z Lets users refer to data in a table using the column and row headings without the need to create a name z Make sure that the “Accept Labels in Formulas” is activated in the Tools/Options Calculation tab . z Limitations: z headings should not have blank spaces z need to re-edit the formula to include changes in data ranges
CEE 3804 - Rojiani 9 Editing Formulas Overview z To edit a formula: z press F2 or double-click on the cell dependent cell references are color coded to simplify editing can dependent cell references with the mouse z Can edit formula in the formula palette result will be updated as the formula is edited

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
10 Editing Formulas Decoding Error Values z Excel errors begin with a “#” sign: z #DIV/0 z #N/A z #NAME? z
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 59

04 ExcelFunctions - CEE 3804 Spreadsheet Functions CEE 3804...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online