IF, VLOOKUP, Creating Pivot Tables, Filters, and Charts
Introduction: In this assignment, students will learn how to use the IF function, the VLOOKUP function, create filters, and create pivot tables.
When using the IF function, you use “comparison operators”. These operators are = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to). Text must be enclosed in double quotation marks.
Expert Tip: Use the $ sign to make an absolute argument so you can drag the formula down the entire column – use the F4 key shortcut!
The VLOOKUP function:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
There are four pieces of information that you will need to build the VLOOKUP syntax:
1. The value you want to look up, also called the lookup value.
2. The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
3. The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.
4. Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don't specify anything, the default value will always be TRUE or approximate match.
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
Look at the example In the VLOOKUP sheet of the excel file. While simplistic, it gives you an idea of what the VLOOKUP can do. It is ALWAYS simpler to place data sets side by side, but this can be done between sheets in the same workbook, as seen in this case. Again, we’re always looking for simplicity.
To retrieve our different fields using what we know, in this case, the evidence number, we would use the following.
Device: =VLOOKUP(I4, Table2,3,FALSE)
*Note, within the RawData, the device field is the 3rd column. So our third value in the formula will be 3*
Examiner: =VLOOKUP(I4, Table2,2,FALSE
Retain Device: =VLOOKUP(I4, Table2,4,FALSE)
Position: =VLOOKUP(I4, Table2,5,FALSE)
Cost: =VLOOKUP(I8,Table1,2,FALSE)
Pivot Table:
Select Insert > Pivot table. For select table range, highlight cells from A1:F61. It’s always best to have a new worksheet to place the Pivot Table.
Notice the fields from the Example Dataset appear to the right of the blank spreadsheet. You can filter the data by columns, rows, or values (note that values are reserved for numbers only).
You are hired by ABC company for a big imaging project. ABC company is short on time, as they only have a short window to get this project completed. It’s all hands on deck. You are the project manager. Your team consists of Associates, Senior Associates, Managers and Directors. The client has given you a budget of 9000 dollars for the project. Your team knocks the task out of the park and completes it early! You are looking over the paperwork and notice that some of the information is missing from the chain of custody (COC) forms, but each COC has an evidence number. Luckily, you kept a detailed log in excel of:
A. Each Evidence number assigned
B. who performed the image
C. what type of device was imaged
D. whether you were requested to retain the device
E. the position of the individual who completed the image
F. You will need to calculate the cost of that particular image based on the position bill rate (Per image) before you leave for the day.
To help the client visualize this project, you will be creating some pivot tables.
1. Create a pivot table in a new tab that displays the total amount of images performed per position (label the tab “ImagesPosition”)
a. Which position did the most images?
2. Create a pivot table in a new tab that displays the total amount of images performed per person (label the tab “ImagesPerPerson”)
a. Who did the most images?
3. Create a pivot table that displays Examiner, Device and how many of each device was imaged per examiner. (label the tab “ExaminerDevice#”)
a. How many Google Pixel 4s did Charles image?
4. Go back to the raw data
a. Are there any duplicates in column A?
b. If so, remove the duplicates before proceeding to part 2 (Data -> Remove Duplicates -> Select Evidence Number).
i. Provide a screenshot of the removed values.
ii. How many unique values remained?
Assignment Part 2 – VLOOKUP, IF Function and Formula (Provide screenshots of your data)
1. To help track down the data for the COC forms, Utilize the VLOOKUP Tab and answer the following Questions:
a. Who performed the following images and how much did each cost the company?
i. DATA-11001
ii. DATA-11992
iii. DATA-00001
iv. DATA-12325
v. DATA-19283
b. What Position was the individual associated with Evidence number DATA-11112?
2. Use the VLOOKUP function to determine the total cost of each image
a. Start in cell F2.
b. Write your function (*HINT, you will use the table with two columns with cost associated)
c. Ensure your formula is applied to the full “Cost” column.
3. Use the Formula tab to determine the total cost of the project. Ensure this value is calculated in cell H64. *Hint, use sum formula(=SUM(), or the formula tab.
a. What was the total cost?
4. Did you meet the budget provided at the start? Do you still have a job? Write an IF function for cell H66. Use the following Criteria.
a. If the budget (H65) is greater than or equal to the total cost of the project (H64), display “Safe…For Now”. If it is over budget, display “YOU’RE fired!”
b. What was the outcome?
Submit your Document write-up with your screenshots along with the finished product of your excel sheet to the course shell!
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
