UGBS 008 Combined pdf..pdf

UGBS 008: Data Processing SESSION 1: Introduction to Data Processing Instructors: STEPHEN.A.ADINGO Email: [email protected] MICHAEL K.KOLUGU Email: [email protected] College of Education School of Continuing and Distance Education ...

UGBS 008: Data Processing SESSION 1: Introduction to Data Processing Instructors: STEPHEN.A.ADINGO Email: [email protected] MICHAEL K.KOLUGU Email: [email protected] College of Education School of Continuing and Distance Education  Outline The Definition of Data Processing Stages of Data Processing Differences between Data and Information Data Processing cycle Data storage hierarchy Importance of data processing 2021_2022 7/25/2022 Slide 2  1.1 Defining Data Processing These are raw or unorganized facts which represent an idea, object, a condition or a situation and can be processed by the computer. Types of Data – Text data: This consists of alphabetic letters, numbers, and special characters. They are typically entered to produce output such as letters, e-mail messages and reports. – Graphics data: This consists of still images, including photographs, mathematical charts, and drawings such as illustrations. – Audio data: This refers to sound, such as voice and music – Video data: This refers to moving pictures and images, such as a videoconference, film clip or full-length movie 2021_2022 7/25/2022 Slide 3  Data Representation in Computers Type of Data Inside computers Text a, b, c 01100001,01100010,01100011 Number 1.2.3…. 00000001,00000010,00000011 Sound 01001100010101000110100… Image 10001001010100000100111... Video 0110000001001101011001… 2021_2022 7/25/2022 Slide 4  Data Vs Information Information is a summarized/processed/manipulated data that is meaningful and useful for decision making. Characteristics of information: – Relevant – Accurate – Complete – Concise – Timely – Cost effective 2021_2022 7/25/2022 Slide 5  1.2 Stages of Data Processing 1. Data Collection 2. Data Preparation 3. Data inputting 4. Data Processing 5. Data Outputting 2021_2022 7/25/2022 Slide 6  1.3 Differentiate between Data and Information Data Information A collection of A processed data that unorganized facts in the contains patterns, form of figures, text, associations, or images, audio, video, and relationships to enable symbols. decision-making 2021_2022 7/25/2022 Slide 7  What is Data Processing ? Data processing is a series of actions that convert data into useful information. It involves the collection and transformation of data items to produce meaningful information. For example, by collecting and processing students’ course registration data for classroom allocation; we can refer to this process as data processing. 2021_2022 7/25/2022 Slide 8  1.4 Data Processing Cycle 3-main stages 2021_2022 7/25/2022 Slide 9  Information Processing Cycle (IPC) The Information Processing Cycle refers to the order of events that go into processing information, including input, processing, storage and output. It may includes a fifth stage, which consists of distribution of information. These are: 1. Receiving Stage ( Input Operation): 2. Processing Data (Processing Operations) 3. Display Information (Output Operation) 4. Storing Information (Storage Operation) 5. Distribution of Information: 2021_2022 7/25/2022 Slide 10  1.5 Data Storage Hierarchy Bit: a binary digit (0 or 1) n 2n 8 28=256 n 2n 9 29=512 0 20=1 10 210=1024 1 21=2 2 22=4 11 211=2048 3 23=8 12 212=4096 4 24=16 5 25=32 20 220=1M 6 26=64 30 230=1G 7 27=128 40 240=1T 2021_2022 7/25/2022 Slide 11  Units of Data Storage Bit: a binary digit (0 or 1) Byte: 8 bits ;Basic storage unit in computer system Kilobyte: 210bytes = 1024 bytes ≈ 103 bytes – Example: 3 KB ≈ 3 x 10 bytes 3 Megabyte: A megabyte (MB) is 220 = 1,048,576 1 Million Bytes Example: 3 MB ≈ 3 x 106 bytes Gigabyte: A Gigabyte (GB) is 230 = 1,073,741,824  1 Billion – Example: 3 GB 3 x 109 bytes Terabyte: : 240 bytes ≈ 1012 byte – Example: 3 TB ≈ 3 x 1012 bytes 2021_2022 7/25/2022 Slide 12  Units of Data Storage 2021_2022 7/25/2022 Slide 13  Other Related Bytes – A nibble is a half-byte (4-bit) - hex representation – A word is a 2-byte (16-bit) data item – A doubleword is a 4-byte (32-bit) data item – A quadword is an 8-byte (64-bit) data item – A paragraph is a 16-byte (128-bit) area 2021_2022 7/25/2022 14  1.6. Importance of Data processing Generate information by converting data into meaningful facts Source of evidence of activities for accountability Add value to an otherwise facts/figures Condense or reduce volume of data Identifying patterns that are not easily detected from data Making informed decisions because of the insight obtained from the processed data Planning for short, medium and long term Projection or forecasting based on the information processed 2021_2022 7/25/2022 15 Any 7/25/2022 Slide 16 2021_2022 7/25/2022 2021_2022 17  UGBS 008: Data Processing 2. Processing Data with MS Excel Lecturer: STEPHEN.A.ADINGO Computer Science Dept./Dept. of Distance Education Contact Email: [email protected] College of Education School of Continuing and Distance Education  Outline Overview of Microsoft Excel Working with basic data formats How to start Microsoft Excel Working with cells and ranges Parts of the Excel window Interpreting error values Entering text, numbers, and Creating a workbook dates in a worksheet 7/31/2022 2 UGBS 008 : 2022 2.0.Introduction: What is a Spreadsheet? An application software that enables users to record, process, and present data in an organised and easily updatable manner. A program that allows you to use data to forecast, manage, predict, and present information. It is a software program that can make number manipulation easy and somewhat painless. 7/31/2022 3 UGBS 008 : 2022  2.1 Overview of Ms Excel Excel is a spreadsheet, a grid made from columns and rows. The nice thing about using a spreadsheet for data processing is that you can experiment with numbers without having to RE-DO all the calculations. 7/31/2022 4 UGBS 008 : 2022  Parts of Ms Excel 7/31/2022 5 UGBS 008 : 2022  Parts of Ms Excel 7/31/2022 6 UGBS 008 : 2022  Basic Components of Excel Spreadsheets are made up of: – Columns – Rows – cells 7/31/2022 7 UGBS 008 : 2022  What is a COLUMN ? In a spreadsheet the COLUMN labeled D is COLUMN is defined as highlighted. the vertical space that is going up and down the window. Letters are used to designate each COLUMN'S location. 7/31/2022 8 UGBS 008 : 2022  What is a row? In a spreadsheet ROW labeled 4 is highlighted. the ROW is defined as the horizontal space that is going across the window. Numbers are used to designate each ROW'S location. 7/31/2022 9 UGBS 008 : 2022  What is a CELL ? A CELL is the space In the above diagram where a row and the CELL labeled C2 is column intersect. highlighted. Each CELL is assigned a name according to its COLUMN letter and ROW number. 7/31/2022 10 UGBS 008 : 2022 A1 B3 E7 UGBS 008 : 2022 7/31/2022 11  12 2.2 How to Start Excel There are Several Methods: Method 1: Method 2 UGBS 008 : 2022 7/31/2022 UGBS 008 : 2022 2.3 Creating a Workbook Excel’s main screen is To create a new workbook: called a “worksheet”. – On the ribbon, click the File tab to display the Backstage view Each worksheet – Click New in the navigation comprised of many bar to display the New boxes, called “cells”. screen – Click the Blank workbook A collection of tile or select an existing template. worksheets is called ‘workbook’ 7/31/2022 13 UGBS 008 : 2022 Creating a Workbook 7/31/2022 14 UGBS 008 : 2022 2.4 Working With Basic Data Format You can organize information by typing a single piece of data into each cell. 7/31/2022 15  Data Types 7/31/2022 16 UGBS 008 : 2022  17 Data Types TYPE EXAMPLES Numeric – values: any number – operators: + - * / ^ % – sample functions: sum( ), average( ), max( ), min( ) etc. Text (aka Character or String) – values: Any group of letters or numbers or special characters. Prefix value in cell with an apostrophe ( ' ) to force a text – operators: & (concatenation) – sample functions: right( ), left(), mid(), lower(), upper(), len(), etc Dates – values: dates and times operators: N/A – sample functions: now( ), today( ), hour(), minute(), etc. Logical (aka boolean) – values: true false – Operators: < > = = – sample functions: if( ), and( ), or( ), not( ), isblank() UGBS 008 : 2022 7/31/2022  2.5 Working with Columns and Rows Find out: How many columns in 1998- 2021 excel ? Find out: How many rows in 2007-2021 excel 7/31/2022 18 UGBS 008 : 2022  19 Selecting a Cell “Select” a cell by clicking on it once (don’t double click). You can move from cell to cell with the arrow keys or by pressing the “Enter” key. UGBS 008 : 2022 7/31/2022  20 Entering Information / The Formula Bar To enter information in a cell, just start typing. When you are done either – Press the Enter Key – Press an arrow key – Click on the “check button” (only visible when entering data into a cell) The information in the selected cell is also displayed in the “formula bar” above the worksheet. UGBS 008 : 2022 7/31/2022 UGBS 008 : 2022 Double Click to Modify a Cell To modify the contents of a cell double click on the cell. Then use the right, left arrow keys and the Insert and Delete keys to Double click to modify the data. change “hi there” to “hello there” When you are done: – Press the Enter key or – Click on the check box. 7/31/2022 21 UGBS 008 : 2022 Data that is “too wide” for a cell The word “Name” is in cell A5 The words “Hours Worked” are in cell B5 (NOT in cell C5). However, since the information is too wide for “Hours cell B5, it looks like it extends Worked” is in cell B5 (look into cell C5. at formula You can determine that the bar) information is really only IN cell B5 by selecting cell B5 and looking at the formula “Hours bar and then selecting cell Worked” is C5 and looking at the NOT in cell C5 (formula formula bar. 7/31/2022 22 bar is empty)  23 Data that is “Chopped Off” If there is information in the You can see the complete data cell to the right, then the by selecting the cell and original cell still contains all looking in the formula bar. of the data, but the data appears to be “chopped off”. UGBS 008 : 2022 7/31/2022  Make a column wider Drag column separator to the right To make Column B wider, point the cursor to the column separator between columns B and column C. The cursor changes to a “Double headed arrow”. Now, click the left mouse button and without letting go of the button, drag the separator to the right to make the column wider (or Column is now wider to the left to make the column narrower). 7/31/2022 24 UGBS 008 : 2022 Getting the Exact Width To get the “exact” width, Double click here double click on the separator instead of dragging it. Column is now EXACTLY the correct width 7/31/2022 25 UGBS 008 : 2022 Resizing a Row Make a row taller or shorter by dragging the separator between the rows. Row is now Click and taller drag here to resize row 5. 7/31/2022 26 UGBS 008 : 2022 Putting an “Enter” inside a cell To add a new line Step 1: Originally “Hours Worked” is inside a cell on one line. – Double click inside the cell where you want the new line. Step 2: Double click to edit cell and then – Press alt-Enter (i.e. press alt-Enter hold down the alt- key and press Enter). – When you are done editing, press Enter (without holding Step 3: Press Enter down alt) to accept (without atl) to accept the changes. the changes. 7/31/2022 27  28 2.6 Working With Cells and Ranges To select a large range of cells, – Click on the upper left cell in the range. – Then hold the shift key and – Click on the lower right cell in the range. You can select different “non-contiguous” areas of cells by – holding down the Ctrl key while – clicking and dragging. UGBS 008 : 2022 7/31/2022 UGBS 008 : 2022 Selecting Non-Contiguous Ranges Click and drag to select the first range. Ctrl-click and drag to select (This cell is also selected additional even though it appears ranges white). 7/31/2022 29  30 Select Entire Columns/Rows/Worksheet To select ENTIRE ROW 2 Tto Select ENTIRE COLUMN B click on “2” row header click on “B” column header Click Click To select COLUMNS B,C,D To select ROWS 2,3 and 5,6,7 click on “B” column header and drag to right drag – and Click click on down drag “2” row header, drag down, Click – then Ctrl-Click on “5” row header and drag down then Ctrl-Click and drag down To select COLUMNS B,C and F,G,H – click on “B” column header, drag to right, To select ENTIRE WORKSHEET – then Ctrl-Click on “F” column header and drag right click on select worksheet button (in corner between “1” and “A” buttons) Ctrl- Click drag Click drag Click UGBS 008 : 2022 7/31/2022  31 Example - continued Step 1: Click on row header for row 5 Step 2: Ctrl-click on row-header for row 11 Step 3: Press Bold button or type ctrl-b UGBS 008 : 2022 7/31/2022 2.7 Common Excel Data Processing Errors 32 UGBS 008 : 2022 7/31/2022  33 Fixing Common Excel Data Processing Errors UGBS 008 : 2022 7/31/2022  34 Excel Assignment 1: 1. Write at least a page notes and not more than 2- pages on MS Excel [10marks] UGBS 008 : 2022 7/31/2022 Any 7/31/2022 Slide 35 UGBS 008 : 2022  7/31/2022 UGBS 008 : 2022 36  UGBS 008: Data Processing 3:Processing Data With Microsoft Excel (Excel Formulas) Computer Science Dept./Dept. of Distance Education Contact Email: [email protected] College of Education School of Continuing and Distance Education  2 Outline Overview of Excel formula Guidelines for writing effective formulas Order of Operator precedence Editing an existing formula Cell references in formula writing Auto filling formulas UGBS 008: 2022 8/7/2022  3 3.1 Overview of Excel Formulas You must have an equal sign ( = ) as the first character in a cell that contains a formula. The = sign tells excel that the contents of the cell is a formula Without the = sign, the formula will not calculate anything. It will simply display the text of the formula. UGBS 008: 2022 8/7/2022  4 Example: Fomulas “tells” Excel that a calculation will need to be performed. UGBS 008: 2022 8/7/2022  5 Operations Used in Excel Formulas You can use any of the following operations in a formula: Operation Symbol Example Addition: + =A1+3 Subtraction: - =100-B3 Multiplication: * =A1*B1 Division: / =D1/100 Exponentiation ^ =A2^2 Negation (subtraction) - =A2-3 UGBS 008: 2022 8/7/2022  6 3.2 Guidelines for Writing Effective Formulas Keep them simple Do not hide data values within formulas – Break up formulas to show intermediate results You can use both explicit values and cell references in a formula An explicit value is also called a literal value – Formula with only cell references: =A1*B1 – Formula with only literal values: =100/27 – Formula with both cell references and literal values: =A1/100 UGBS 008: 2022 8/7/2022  7 3.3 Order of Operations in Excel When using several operations in one formula, Excel follows the order of operations for Maths. – First: all parentheses - innermost first – Second: exponents (^) – Third: all multiplication (*) and division (/). Do these starting with the leftmost (*) or ( / ) and work to the right. – Fourth: all addition (+) and subtraction (-). Do these starting with the leftmost + or - and work to the right. UGBS 008: 2022 8/7/2022  8 Order of Operations in Excel The sentence "Please excuse my dear aunt Sarah" is a popular mnemonic to remember the order of operations: Menumonic Meaning – Please parentheses – Excuse exponents – My Dear multiplication and division (going left to right) – Aunt Sarah addition and subtraction (going left to UGBS 008: 2022 right) 8/7/2022  9 Complex Formulas You can use several operations in one function You can group those operations with parentheses Examples =3*2+1 =C1*(A1+B1) =(100*A2-10)+(200*B3-20)+30 =(3+2*(50/B3+3)/7)*(3+B7) UGBS 008: 2022 8/7/2022  10 3.4 Editing a Formula UGBS 008: 2022 8/7/2022  11 3.5 Cell Referencing Cell on another sheet: sheetName!cellReference Examples sheet2!A1 Range on another sheet: sheetName!range Examples sheet2!B4:C8 Row on another sheet: sheet Name! row: row Examples '2019 Sales'!2:2 Column on another sheet: sheetName!column: column Examples '2002 Forecasts'! B:B If a sheet name has a space in it, you must surround the sheet name with apostrophes (i.e. single quotes) Examples =sum('2202 Forecasts'!f:f) UGBS 008: 2022 8/7/2022  12 More examples Add up values from 2 different sheets =sum ( 'great stocks'!b2:c4, 'so so stocks'!b2:c4) This next one is a little confusing =sum (a1,a!a1,b1:b4,b1!b4,c!c:c) Explanation a1 this is a cell reference on the current sheet a!a1 "a" is the name of sheet. "a1" is a cell on the "a" sheet b1:b4 this is a range on the current sheet b1!b4 "b1" is the name of a sheet. "b4" is a cell on the "b1" sheet c!c:c “c" is the name of a sheet. “c:c" is all of the cells in the c column on the “c” sheet UGBS 008: 2022 8/7/2022  13 Types of Cell References By default, when you copy a formula that contains a cell reference, excel will automatically adjust the cell reference. You can stop Excel from automatically adjusting the cell reference by using one or more dollar signs ($) in the cell reference. These are called absolute cell references. A cell reference without a dollar sign is a relative cell reference. UGBS 008: 2022 8/7/2022  14 Relative Cell Reference d9 This is a "relative cell reference". – Changing the column: If you copy this cell reference to another cell: the "d" will increment one letter for every cell that is move over to the right. The "d" will decrement one letter for every cell that I move over to the left – Changing the row: If you copy this cell reference to another cell: the "9" will increment by one for every cell that I move down. The "9" will decrement by one for every cell that I move up UGBS 008: 2022 8/7/2022  15 Relative References UGBS 008: 2022 8/7/2022  16 Absolute cell reference $d$9 This is an absolute cell reference. – If you copy a formula with this cell reference, the cell reference will NOT change AT ALL. UGBS 008: 2022 8/7/2022  17 Absolute cell reference UGBS 008: 2022 8/7/2022  18 Mixed References UGBS 008: 2022 8/7/2022  19 3.6 Auto Filling Formulas and Data AutoFill provides a quick way to enter content and formatting in cells based on existing entries in adjacent cells. After you select a range, a fill handle appears in the lower-right corner of the selection. When you drag the fill handle over an adjacent cell or range, AutoFill copies the content and formats from the original cell or range into the adjacent cell or range. More efficient than the two-step process of copying and pasting. By default, AutoFill copies both the content and the formatting of the original range of the selected range UGBS 008: 2022 8/7/2022  20 Filling a Series AutoFill technique can create a series of numbers, dates, or text based on a pattern. The fill series option learns the pattern of data provided and provides subsequent values. UGBS 008: 2022 8/7/2022  21 Assignment 2: Enter the information below into and excel and used it to answer the questions that follow in the same sheet Compute the following: a) Monthly Average Expenses in cells B7,C7 and D7 b)The Total Expenses in cell B12 c)The Total Income in cell B13 UGBSd)The 008: 2022Balance in cell B14 8/7/2022  8/7/2022 UGBS 008: 2022 22  UGBS 008: Data Processing 4: Processing Data With Microsoft Excel Functions Computer Science Dept./Dept. of Distance Education Contact Email: [email protected] College of Education School of Continuing and Distance Education  2 Outline Overview of Excel functions Categories of Excel functions How to use Excel functions Basic Excel functions – Statistical functions – Text functions – Logical functions Comparison operators 2021_2022 14/08/2022 7:56 am  3 4.1 Overview of Functions An excel function is a "named operation“ Pre-written excel operations that augments formulas Functions have – a name – parentheses – parameters/arguments you can have many parameters for one function separated with commas (,) The number of parameters is one more than the number of commas. 2021_2022 14/08/2022 7:56 am  4 Functions Terminologies Consider : The name of the function SUM(1,2,3,4,5) – Function name is "SUM" – The parameters or arguments to this function are 1,2,3,4 and 5 – The entire thing, i.e. SUM(1,2,3,4,5), is a function call – The value of this function call is 15. Another way to say this is that this function call returns 15. 2021_2022 14/08/2022 7:56 am  1-5 Some Notes on the Functions Components Function Syntax Function name (spelling), Right order of different function parts (argument sequence.) Proper use of comma, parentheses and other punctuations. Function Arguments (parameters) The values/ cell references, the function needs to work upon for result calculation. Number of arguments could be fixed or unlimited depending upon the nature of the function used. Function Result Every function results only in one value.  6 4.2 Categories Of Functions In Excel There are a total of 333 plus functions in Excel. These functions are separated into 12 categories as follows: 2021_2022 14/08/2022 7:56 am  7 4.3 How to work with functions Insert a Function Writes a Function 2021_2022 14/08/2022 7:56 am  8 4.4 Basic Excel Function Statistical SUM, SUMIF, AVERAGE, MIN, MAX, COUNT, COUNTBLANK, COUNTA COUNTIF& COUNTIFS 2021_2022 14/08/2022 7:56 am  9 The SUM Function Function Function Result =SUM(1,2,3,4,5) 15 =SUM(a1,b1,c1) a1+b1+c1 =SUM(9,a1,b2,5,c1) 9+a1+b2+5+c1 Ranges can be specified as a parameters to a function call. Both of the following function calls produce the same result as without a range =SUM(a1,b1,c1,a2,b2,c2,a3,b3,c3,a4,b4,c4) with a range 2021_2022 =SUM(a1:c4) 14/08/2022 7:56 am  Sum Functions –( SUM & SUMIF) To sum or add a range of cells, use the SUM function. To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).  AVERAGE Functions: ( Average & Average IF To calculate the average of a range of cells, use the AVERAGE function. To average cells based on one criteria, use the AVERAGEIF function. For example, to calculate the average excluding zeros. Note: means not equal to  Count Functions: (COUNT , COUNTBLANK & COUNTA) To count the number of cells that contain numbers, use the COUNT function. The COUNTBLANK counts only empty cells The COUNTA function counts the number of data cells from A2 to A7. It returns 5 because cell A5 is blank. Hence, all the values are counted except the blank value of cell A5.  Count Functions: (COUNTIF & COUNTIFS) To count cells based on one criteria (for example, higher than 9), use the COUNTIF function. Note: in contrast to the COUNT function, cells can contain text as well. To count cells based on multiple criteria (for example, green and higher than 9), use the COUNTIFS function.  STEDV Function To calculate the standard deviation, use the STEDV function.  MEDIAN & Mode Function To find the median (or middle number), use the following MEDIAN function. Check: To find the most frequently occurring number, use the following MODE function  MIN Function & MAX Function To find the minimum value, use the MIN function. To find the maximum value, use the MAX function.  SMALL & LARGE Functions To find the for example, the second smallest number, use the following SMALL function Check: To find the third largest number, use the following LARGE function.  18 Text Function TEXT right( ) left( ) mid( ) concatenate( ) lower( ) upper( ) len( ) 2021_2022 14/08/2022 7:56 am  19 RIGHT & LEFT Functions: RIGHT ( , ) Formula View Values View 2021_2022 14/08/2022 7:56 am  20 MID (, , ) This example extracts the second through the fourth characters from the original text value: Formula View Values View 2021_2022 14/08/2022 7:56 am  21 Concatenation: Concatenate (&) Function Use & to combine (or concatenate) two different text values You can use the CONCATENATE function instead of the ampersand (&). The following formulas are equivalent: =A1&B1&C1 =CONCATENATE(A1,B1,C1) The CONCATENATE function can take as many parameters as you like. 2021_2022 14/08/2022 7:56 am  22 Examples: Concatenate (&) Function Formula View Notice that there is no Values View space between the two values 2021_2022 14/08/2022 7:56 am  23 Concatenating spaces - Example You can concatenate spaces into a formula Formula View Values View values contain spaces 2021_2022 14/08/2022 7:56 am  24 Putting it all together In this example we concatenate periods into the initials. Formula View Values View The initials now contain periods 2021_2022 14/08/2022 7:56 am  25 Lower ( ) & Upper ( ) LOWER converts text to lower case. UPPER converts text to upper case. Example: Formula View Values View 2021_2022 14/08/2022 7:56 am  26 LEN ( ) LEN returns a numeric value equal to the number of character in a text value (i.e. the “length” of the text value). Spaces ARE included in the length. Example Formula View Values View 2021_2022 14/08/2022 7:56 am  27 LEN ( ) LEN returns a numeric value equal to the number of character in a text value (i.e. the “length” of the text value). Spaces ARE included in the length. Example Formula View Values View 2021_2022 14/08/2022 7:56 am  4.5 Logical (aka Boolean) Functions The following statements are The following statements TRUE: are FALSE: Fish live in water. Deer live on land. Fish live on land. Deer live in water. 3 is greater than 2 2 is less than 3 2 is greater than 3 2 is less than or equal to 3 3 is less than 2 2 is less than or equal to 2 3 is greater than or equal to 2 3 is less than or equal to 2 3 is greater than or equal to 3 2 is greater than or equal to 3 2 is equal to 2 2 is equal to 3 2 is not equal to 3 2 is not equal to 2 2021_2022 14/08/2022 7:56 Slide 28 am  29 Logical Function IF, NestedIf Lookup. 2021_2022 14/08/2022 7:56 am  30 Using the IF function Syntax: =IF (logical_test, [value_if_true,], [value_if_false]) Example: To determine if a student has passed or failed in an exams, the formula is expressed as: =IF (A2>=50, “PASS”, “FAIL”) 2021_2022 14/08/2022 7:56 am  31 IF with a numerical result Formula View Values View 2021_2022 14/08/2022 7:56 am  Logical Functions - the IF Function The IF Function checks whether a condition is met, and returns one value if TRUE and another value if FALSE. Example: Select cell C2 and enter the following function. The IF function returns Correct because the value in cell A1 is higher than 10.  Using Nested IFs The nested IF Function is simply an IF comprised of another IF functions The syntax of the IF function is: =IF (logical_test, [value_if_true,], IF (logical_test), [value_if_true],...., [value_if_true]) Example: If the grading system in University of Ghana states that a student’s mark between 100 and 80 is grade A, 79 to 60 is B, 59 to 50 is C, and 49 is F. IF (A2>=80, “A”, IF (A2>=60, “B”, IF (A2>=50, “C”, “F”))) 33 2021_2022 14/08/2022 7:56 am  Creating Nested IFs 34 2021_2022 14/08/2022 7:56 am  35 Using the AND Logical Operator The following is TRUE Fish live in water AND deer live on land. The following are all FALSE Fish live in water AND deer live in water. Fish live on land AND deer live on land. Fish live on land AND deer live in water. Takes any number of parameters Returns TRUE if ALL of the parameters evaluate to TRUE otherwise returns FALSE. Syntax: 2021_2022 14/08/2022 7:56 am  36 AND Formula View Values View 2021_2022 14/08/2022 7:56 am  37 IF with AND - nested function calls You can use an AND inside of an IF. This is called a NESTED FUNCTION CALL Example =IF( AND (A2>A3,B2B3) , 500, 1000) AND is "nested" inside of the IF These parentheses "belong to" the if 2021_2022 14/08/2022 7:56 am  38 IF with AND - spreadsheet views Formula View Values View 2021_2022 14/08/2022 7:56 am  39 Using the OR and NOT Functions OR Function Takes any number of parameters Returns TRUE if ANY of the parameters evaluate to TRUE otherwise returns FALSE Syntax: NOT Function: Takes ONLY ONE parameter Returns the "opposite" of the value of the parameter – returns FALSE if the parameter value is TRUE – returns TRUE if the parameter value is FALSE 2021_2022 14/08/2022 7:56 am  40 TRUTH TABLES FOR AND & OR AND TRUE FALSE TRUE True False FALSE False False Fish live in water OR deer live in water. OR TRUE FALSE TRUE True True FALSE True False 2021_2022 14/08/2022 7:56 am  41 Complex Nested Function Calls Examples: =IF(AND(A2>A3, OR(B2=B3,C2A3, OR(B2=B3,C2A3, NOT(OR(B2=B3,C2 greater than < less than >= greater than or equal to