Introduction to Information Systems
Lab 02: Business Analytics using Microsoft Excel
Learning Objectives
1. Able to use Excel Built-in Functions for Business Analytics.
Copyright By PowCoder代写 加微信 powcoder
2. Able to use Conditional Formatting to highlight important information.
Introduction
Microsoft Excel is a desktop application for data organization and comparison. For example, you may use it for monitoring your personal monthly expenses and checking the balance whether it is out of budget by the end of month. Your personal data are formatted in terms of rows and columns in an Excel’s worksheet.
In this laboratory, you will learn how to use some advanced built‐in functions to analyze presented data.
Part 1: Excel Built-in Functions
Function AND
COUNTIF SUMIF VLOOKUP
1. AND Function
Description
Returns TRUE if all of its arguments are TRUE
Returns TRUE if any argument is TRUE
Use this function to return one value if a condition is true and another value if it’s false
Counts the number of cells within a range that meet the given criteria
Adds the cells in a range that meet the given criteria
Use this function when you need to find things in a table or a range by row
Syntax: AND(logical1, [logical2], …) Example:
=AND(TRUE, TRUE) TRUE =AND(TRUE, FALSE) FALSE =AND(A2=A4-A3, A3=2)
=AND(A5-A3=A3, A4=3, A2=A4)
2. OR Function
Syntax: OR(logical1, [logical2], …) Example:
=OR(TRUE, FALSE) TRUE =OR(FALSE, FALSE) FALSE =OR(A2=A4-A3, A3=2)
=OR(A5-A3=A1, A4=3, A2=A4)
Introduction to Information Systems
3. IF Function
Syntax: IF(logical_test, [value_if_true], [value_if_false]) Example:
=IF(B2<>“”, “No Error”, “Missing Category”) No Error =IF(C2>0, “No Error”, “Invalid Price”) No Error
Q1. How to change the IF Function so that it gives out the same result as “=IF(C2>0, “No Error”, “Invalid Price”)”? Hint: =IF(_________, “Invalid Price”, __________)
Q2. How to combine the below two IF Functions into one Function?
=IF(B5<>“”, “No Error”, “Missing Category”) =IF(C5>0, “No Error”, “Invalid Price”)
Hint: Using 1 AND Function and 2 IF Function Expected Result:
4. COUNTIF Function
Syntax: COUNTIF(range, criteria) Example:
Missing Category Invalid Price
5. SUMIF Function
Syntax: SUMIF(range, criteria, [sum_range])
=COUNTIF(B2:B7, “Stationery”) 4 =COUNTIF(B2:B7, B4)
=COUNTIF(C2:C7, “>=150”)
=SUMIF(A2:A7, “Desk”, C2:C7) 550 =SUMIF(B2:B7, B2, C2:C7)
Default value of range_lookup is TRUE
Introduction to Information Systems
6. VLOOKUP Function
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Example:
=VLOOKUP(600, A2:D4, 2, TRUE) Silver =VLOOKUP(600, A2:D4, 2, FALSE) #N/A =VLOOKUP(“Gold”, B2:D4, 2, FALSE)
Q1. How to write the VLOOKUP Function to find out the corresponding Member Type of Kitty? Ans:
Q2. If the member is new member, he/she can enjoy the Discount. How to write the VLOOKUP Function to find out the Discount Rate of Tony? (Hint: Using IF Function)
Part 2: Conditional Formatting
Conditional Formatting quickly highlights important information in a spreadsheet. You may use the built-in formatting rules or add your own formula to a conditional formatting rule.
Built-in formatting rules:
Introduction to Information Systems
Your own formula:
Highlight Cells Rules example:
Data Bars example:
Challenge Questions [Optional]
Q1. How to write the IF Function so that it gives out the below expected result? Expected Result:
Introduction to Information Systems
Q2. How to write the IF Function so that it gives out the below expected result? Hint: Using Array Formula, Press Ctrl+Shift+Enter to enter the formula Expected Result:
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com