Tutorial 7 Using Advanced Functions and Conditional Formatting

Tutorial 7: Using Advanced Functions and Conditional Formatting Microsoft Excel 2010 ® ®

Objectives • • • XP Use the IF function Use the AND function Use the OR function Use structured references in formulas Nest the IF function Use the VLOOKUP function Lecturer Yusuf Hussein Mohamed 2

What is excel XP • Microsoft originally marketed a spreadsheet program called Multiplan in 1982, which was very popular on CP/M systems, but on MS-DOS systems it lost popularity to Lotus 1 -2 -3 • the first version of Excel was released for the Mac in 1985 and the first Windows version (numbered 2. 0 to line-up with the Mac and bundled with a run-time Windows environment) was released in November 1987 • The history of MS Office starts officially from November 19 1990 when Office for Windows (also called MS Office 1. 0 Lecturer Yusuf Hussein Mohamed 3

WHAT IS EXCEL XP • Microsoft Excel spreadsheet is an interactive computer application program for organization and analysis of data in tabular form. • Excel is one of the Microsoft Office suites of applications. We commonly use it to organize and manipulate numeric information. Electronic spreadsheet organizes information into software defined columns and rows Lecturer Yusuf Hussein Mohamed 4

Excel Accounting XP • Microsoft Office Excel was designed to support accounting functions such as budgeting, preparing financial statements and creating balance sheets. It comes with basic spreadsheet functionality and many functions for performing complex mathematical calculations. Lecturer Yusuf Hussein Mohamed 5

XP Lecturer Yusuf Hussein Mohamed 6

XP • Versions of Excel up to 7. 0 had a limitation in the size of their data sets of 16 K (214 = 16384) rows. Versions 8. 0 through 11. 0 could handle 64 K (216 = 65536) rows and 256 columns (28 as label 'IV'). Version 12. 0 can handle 1 M (220 = 1048576) rows, and 16384 (214 as label 'XFD') columns. Lecturer Yusuf Hussein Mohamed 7

Importance of MS Excel and Uses. XP • MS Excel is used for various purposes by business establishments. • MS Excel has a simple interface that allows users to easily understand this software and also perform basic activities. • . One of the major advantages is its flexibility feature. its very easy to use and even a novice user can use this software. • its commonly used for financially-related activities. Lecturer Yusuf Hussein Mohamed 8

XP • its popularity is that the user can define custom formulas • This spreadsheet software also helps the individuals and professionals to effectively keep a track of sales leads, project status reports, and invoice reports. • Microsoft Excel is also very popular among professionals from science background as it allows them to easily work with statistical formulas and graphing. Lecturer Yusuf Hussein Mohamed 9

XP Lecturer Yusuf Hussein Mohamed 10

CELL REFERENCING RELATIVE REFERENCE IN CELL (C 1) SUM FUNCTION IS USED. THEN FUNCTION FROM CELL (C 1) IS COPY TO CELL (D 3). WHEN THE POSITION OF THE CELL IS CHANGED FROM (C 1) TO (D 3), THEN THE REFERENCE IS ALSO CHANGED FROM (A 1, B 1) TO (B 3, C 3). MS EXCEL 05 -11 -2020 A RELATIVE CELL REFERENCE AS (A 1) IS BASED ON THE RELATIVE POSITION OF THE CELL. IF THE POSITION OF THE CELL THAT CONTAINS THE REFERENCE CHANGES, THE REFERENCE ITSELF IS CHANGED. 11

CELL REFERENCING ABSOLUTE REFERENCE IN CELL (C 1) SUM FUNCTION IS USED. THEN FUNCTION FROM CELL (C 1) IS COPY TO CELL (D 3). WHEN THE POSITION OF THE CELL IS CHANGED FROM (C 1) TO (D 3), THEN THE ABSOLUTE REFERENCE REMAINS THE SAME(A 1, B 1). $ IS USED FOR CONSTANT ROW OR COLUMN. MS EXCEL 05 -11 -2020 AN ABSOLUTE CELL REFERENCE AS ($A$1) ALWAYS REFERS TO A CELL IN A SPECIFIC LOCATION. IF THE POSITION OF THE CELL THAT CONTAINS THE FORMULA CHANGES, THE ABSOLUTE REFERENCE REMAINS THE SAME. 12

CELL REFERENCING MIXED REFERENCE A MIXED REFERENCE HAS EITHER AN ABSOLUTE COLUMN AND RELATIVE ROW OR ABSOLUTE ROW AND RELATIVE COLUMN. AN ABSOLUTE COLUMN REFERENCE TAKES THE FORM $A 1, $B 1. AN ABSOLUTE ROW REFERENCE TAKES THE FORM A$1, B$1. Microsoft Excel 2010 ® ® IN CELL (C 1) SUM FUNCTION IS USED. THEN FUNCTION FROM CELL (C 1) IS COPY TO CELL (D 3). WHEN THE POSITION OF THE CELL IS CHANGED FROM (C 1) TO (D 3), THEN ROW REFERENCE IS CHANGED(FROM 1 TO 3) BUT COLUMN REFERENCE REMAINS SAME(A, B). MS EXCEL 05 -11 -2020 13

XP Lecturer Yusuf Hussein Mohamed 14

COMMONLY USED EXCEL FUNCTIONSXP Function name Description Syntax SUM Displays the sum of the range =SUM(range) AVERAGE Displays the average value of the range =AVERAGE(range) MAX Determines the LARGEST value in the range =MAX(range) MIN Determines the SMALLEST value in =MIN(range) the range COUNT Calculates the number of values in =COUNT(range) the range

Formula Elements. XP • Equal sign (starts all formulas). • Cell references (also defined names, sheet references, workbook references, • Math operators. • Numbers (if the number will not change; for example, 12 months, 24 hours). • Functions (IF, MATCH, INDEX, COUNTIF, and so on). • Lecturer Yusuf Hussein Mohamed 16

Continuous XP • Function argument elements (such as a 0 in the third argument of MATCH to tell function to do an "Exact Match" lookup. ). • Comparative operators. • Join operator: Ampersand (join symbol: &). • Text within quotation marks (for example, "For the Month Ended"). • Array constants (for example, {1, 2, 3}). Lecturer Yusuf Hussein Mohamed 17

Excel error XP • Formulas with errors will have a green triangle on the cell, and a symbol on the right when the cell is selected that may offer help resolving the issue – #Value! Indicates that there is an error with the formula – in this case there is a number being multiplied by a cell that contains text – #Ref! Indicates that there is an invalid cell reference – usually because a formula has been copied or moved – ###### Indicates that the value to be displayed is larger than will fit in the cell

XP Lecturer Yusuf Hussein Mohamed 19