04 ExcelFunctions

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

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

View Full Document Right Arrow Icon
CEE 3804: Spreadsheet Functions CEE 3804 Computer Applications for Civil and Environmental Engineers
Background image of page 1

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

View Full DocumentRight Arrow Icon
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
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
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
Background image of page 4
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
Background image of page 5

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

View Full DocumentRight Arrow Icon
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
Background image of page 6
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
Background image of page 7

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

View Full DocumentRight Arrow Icon
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
Background image of page 8
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
Background image of page 9

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

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

Page1 / 59

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

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

View Full Document Right Arrow Icon
Ask a homework question - tutors are online