CST8118 Computer Essentials: MS Excel Functions PDF

CST8118 Computer Essentials Exploring MS Excel Functions  Week 10 Exploring MS Excel Functions Where do I find more functions? Where do I find help on functions? Logic Functions: TRUE, FALSE, NOT, AND, OR, IF, SWITCH Text Functions TRIM, UPPER, LOWER, PROPER, CONCAT,...

CST8118 Computer Essentials Exploring MS Excel Functions  Week 10 Exploring MS Excel Functions Where do I find more functions? Where do I find help on functions? Logic Functions: TRUE, FALSE, NOT, AND, OR, IF, SWITCH Text Functions TRIM, UPPER, LOWER, PROPER, CONCAT, CHAR Example: Generate Batch File Date & Time Functions WEEKDAY(value), NOW() 2  Welcome This presentation will introduce additional Microsoft Excel functions, including logic, text, and date functions. 3  Where do I locate more functions? (1) Use the Formulas Tab in the Ribbon to see function categories (2) More Functions (2) category has a drop-down subcategory Engineering (3) Engineering functions would be of interest to Programmers to start exploring. 4  Where do I locate more functions (alternative)? (1) Use the Formulas Tab in the Ribbon to see function categories (2) Select a Cell then use the Insert Function option (3) An Insert Function dialog opens where you can select functions from a list 5  Where do I get help for Functions? Mouse Tool-Tips If using the Ribbon hovering the mouse over a function name will provide a tool-tip with brief information. The tool-tip will also have a link “Tell me more” which opens the help system. Function Dialog If using the Insert Function Dialog, there is a link at the bottom “Help on this function” which will open a web document related to the selected function. 6  Logic Functions The logic functions handle decision making using boolean math. Some logic functions you might commonly use: TRUE, FALSE, NOT, AND, OR, IF, SWITCH See LogicFunctionExamples.xlsx for examples and notes, as well as the next several slides. Also see Level 1 Course Grade Predictor (Optional Practice) Version 2.xlsx, specifically the CST8118 tab which uses IF and OR to either enter a score of zero for failing the lab or theory components, or the actual score in the case of a passing grade. 7  Logic Functions - TRUE TRUE =TRUE() Result: TRUE The TRUE() function simply returns the value TRUE 8  Logic Functions - FALSE FALSE =FALSE() Result: FALSE The FALSE() function simply returns the value FALSE TRUE and FALSE will also be interpreted as TRUE() and FALSE(). 9  Logic Functions - NOT NOT =NOT(TRUE()) Result: FALSE =NOT(FALSE()) Result: TRUE NOT reverses the logic, i.e., NOT(TRUE()) is FALSE, NOT(FALSE()) is TRUE 10  Logic Functions - AND AND Reference Cell B14 holds value of 42 =AND(B14=42,B14