Data Analytics Course 2 Week 3

Cell Reference

A cell or range of cells in a worksheet that can be used in a formula EX: A7

#Error (in google sheets only)

A formula can't be interpreted as input (also known as parsing error) You just typed your formula wrong Delimiter: it indicates the end of each data item. Example a comma is a delimiter (Google Sheets only) Something can't be interpreted as it has been input. This is also known as a parsing error. EX:=COUNT(B1:D1 C1:C10) is invalid because the cell ranges aren't separated by a comma

Difference between formulas and functions

A formula is a set of instructions used to perform a calculation using the data in a spreadsheet. A function is a preset command that automatically performs a specific process or task using the data in a spreadsheet.

#REF!

A formula is referencing a cell that is no longer valid or has been deleted A formula is referencing a cell that isn't valid EX: A cell used in a formula was in a column that was deleted

#DIV/0! Error

A formula is trying to divide a value in a cell by 0 or an empty cell Can use an IFerror formula to fix the error or to show a different value if there is an error A formula is trying to divide a value in a cell by 0 (or an empty cell with no value) EX: =B2/B3, when the cell B3 contains the value 0

#NUM!

A formula or function calculation can't be performed as specified The formula name or function isn't recognized The data doesn't make sense for that calculation The spreadsheet can't perform a formula calculation because a cell has an invalid numeric value EX: =DATEDIF(A4, B4, "M") is unable to calculate the number of months between two dates because the date in cell A4 falls after the date in cell B4

#NAME

A formula or function name isn't understood Typo in formula or function EX: VLOOOOKUP vs VLOOKUP The name of a formula or function used isn't recognized EX: The name of a function is misspelled

#VALUE!

A general error that could indicate a problem with a formula or referenced cells Often not clear right away what the error is, so it might take a little more time to fix A general error indicating a problem with a formula or with referenced cells EX: There could be problems with spaces or text, or with referenced cells in a formula; you may have additional work to find the source of the problem. If you are working with Microsoft Excel, an interactive page,How to correct a #VALUE! error, can help you narrow down the cause of this error. You can select a specific function from a drop-down list to display a link to tips to fix the error when using that function.

Formula

A set of instructions that perform a specific calculation. Basically formulas do the math for you. Formulas don't only do math, they can do a lot more

Absolute Referencing

Absolute referencing is marked by a dollar sign ($). For example, =$A$10 has absolute referencing for both the column and the row value Relative references (which is what you normally do e.g. "=A10") will change anytime the formula is copied and pasted. They are in relation to where the referenced cell is located. For example if you copied "=A10" to the cell to the right it would become "=B10". With absolute referencing "=$A$10" copied to the cell to the right would remain "=$A$10". But if you copied $A10 to the cell below, it would change to $A11 because the row value isn't an absolute reference. Absolute references will not change when you copy and paste the formula in a different cell. The cell being referenced is always the same. To easily switch between absolute and relative referencing in the formula bar, highlight the reference you want to change and press the F4 key; for example, if you want to change the absolute reference, $A$10, in your formula to a relative reference, A10, highlight $A$10 in the formula bar and then press the F4 key to make the change.

Scope of Work (SOW)

An agreed upon outline of the work you're going to perform on a project Work details, schedules, and reports the client expects EX: Deliverables, timelines, milestones, and reports With a solid scope of work, you'll be able to address any confusion, contradictions, or questions about the data up- front and make sure these sneaky setbacks don't stand in your way. As a data analyst, it's hard to overstate the importance of an SOW document. A well-defined SOW keeps you, your team, and everyone involved with a project on the same page. It ensures that all contributors, sponsors, and stakeholders share the same understanding of the relevant details.

Spreadsheet (Analyze)

Analyze data in a spreadsheet to help make better decisions. Some of the most common spreadsheet analysis tools include formulas to aggregate data or create reports, and pivot tables for clear, easy-to-understand visuals.

Spreadsheet (Archive)

Archive any spreadsheet that you don't use often, but might need to reference later with built-in tools. This is especially useful if you want to store historical data before it gets updated.

One of the most valuable spreadsheet features is a formula.

As a quick reminder, a formula is a set of instructions that does a specific calculation using the data in a spreadsheet. Formulas make it easy for data analysts to do powerful calculations automatically, which helps them analyze data more effectively.

Combining with functions

COUNTIF() is a formula and a function. This means the function runs based on criteria set by the formula. In this case, COUNT is the formula; it will be executed IF the conditions you create are true. For example, you could use =COUNTIF(A1:A16, "7") to count only the cells that contained the number 7. Combining formulas and functions allows you to do more work with a single command.

Spreadsheet (Capture)

Capture data by the source by connecting spreadsheets to other data sources, such as an online survey application or a database. This data will automatically be updated in the spreadsheet. That way, the information is always as current and accurate as possible

Range of cells

Collection of two or more cells Can include cells from same row or column, or from different columns and rows collected together

Data needs context

Context: Condition in which something exists or happens Actions can be appropriate in some context but inappropriate in others

#N/A

Data in formula can't be found by the spreadsheet Generally it means data doesn't exist Mostly occurs when using functions to find a value (EX: Index Match, Vlookup) Can be caused by typos A formula can't find the data EX: The cell being referenced can't be found

What is a good Scope of Work (SOW)

Deliverables: What work is being done, and what things are being created as a result of this project? When the project is complete, what are you expected to deliver to the stakeholders? Be specific here. Will you collect data for this project? How much, or for how long? Avoid vague statements. Be specific! Use numbers and aim for hard, measurable goals and objectives. Milestones: This is closely related to your timeline. What are the major milestones for progress in your project? How do you know when a given part of the project is considered complete? Timeline: Your timeline will be closely tied to the milestones you create for your project. The timeline is a way of mapping expectations for how long each step of the process should take. The timeline should be specific enough to help all involved decide if a project is on schedule. When will the deliverables be completed? How long do you expect the project will take to complete? If all goes as planned, how long do you expect each component of the project will take? When can we expect to reach each milestone? Reports: Good SOWs also set boundaries for how and when you'll give status updates to stakeholders. How will you communicate progress with stakeholders and sponsors, and how often? Will progress be reported weekly? Monthly? When milestones are completed? What information will status reports contain? SOWs should also contain information specific to what is and isn't considered part of the project. The scope of your project is everything that you are expected to complete or accomplish, defined to a level of detail that doesn't leave any ambiguity or confusion about whether a given task or item is part of the project or not.

Data can be used on many levels. More valuable as you move from descriptive to prescriptive

Descriptive: Answering questions like "what happened" Diagnostic: Why did it happen? Predictive: What will happen? Prescriptive: How can we make it happen?

Spreadsheet (Destroy)

Destroy your spreadsheet when you are certain that you will never need it again, if you have better backup copies, or for legal or security reasons. Keep in mind, lots of businesses are required to follow certain rules or have measures in place to make sure data is destroyed properly.

Few basic strategies to help avoid errors and make your analytics life easier

Filter data to make your spreadsheet less complex and busy. Use and freeze headers so you know what is in each column, even when scrolling. When multiplying numbers, use an asterisk (*) not an X. Start every formula and function with an equal sign (=). Whenever you use an open parenthesis, make sure there is a closed parenthesis on the other end to match. Change the font to something easy to read. Set the border colors to white so that you are working in a blank sheet. Create a tab with just the raw data, and a separate tab with just the data you need.

Keep in mind, not every calculation you'll come across has its own function to help you.

For example, to find the percent change in sales between June and July, you'd use the same formula you used in an earlier video. No function to complete this

Step-by-step in spreadsheets

If your data is a bit messy when you get it, these steps will help you get it ready for analysis First thing you want to do when opening a brand new spreadsheet is to give it a title Make title short, clear, and have it say exactly what the data in the spreadsheet is about Creating a folder on your computer for your spreadsheet and related files makes it easier to find them In google sheets you go to file, then move, then create a new folder for this spreadsheet and related files There's a few different ways data analysts get data they work with. Depending on the job, you might use data from an open source, you might be given data to work with or you might be asked to find your own data. There is a lot of open data sources online where data is made to the public EX: World bank, World health organization, Google public data explorer, and the U.S. Census Bureau Make sure to add attributes at the top of a column to show what the data underneath it represents Make sure to format attribute cells to make it stand out from the rest (bold, fill it in with a color, etc.) Can add borders to data as well to make is easier to see each piece of information within your spreadsheet Organizing a worksheet before you analyze can help you focus on the data once you start your analysis

Autofilling

Lower right corner of a cell has a handle you can use to drag down formula If you want to create a numbered sequence in a column or row, do the following: 1) Fill in the first two numbers of the sequence in two adjacent cells, 2) Select to highlight the cells, and 3) Drag the fill handle to the last cell to complete the sequence of numbers. For example, to insert 1 through 100 in each row of column A, enter 1 in cell A1 and 2 in cell A2. Then, select to highlight both cells, click the fill handle in cell A2, and drag it down to cell A100. This auto-fills the numbers sequentially so you don't have to type them in each cell.

Formulas are built on operators

Operator: A symbol that names the type of operation or calculation to be performed EX: Plus sign Formulas used as a data analyst will most likely include at least one operator

Example of spreadsheet tasks

Organize your data per the task you've been given Might put your data in a pivot table Might sort and filter data in your pivot table Very common in most jobs After organizing and filtering data you can perform calculations to learn more about it Formulas Functions Formulas and functions are great for doing some quick math

Spreadsheet (Plan)

Plan for the users who will work within a spreadsheet by developing organizational standards. This can mean formatting your cells, the headings you choose to highlight, the color scheme, and the way you order your data points. When you take the time to set these standards, you will improve communication, ensure consistency, and help people be more efficient with their time.

Data Life Cycle

Plan, Capture, Manage, Analyze, Destroy, and Archive

Function

Preset command that automatically performs a specific process or task using the data

Before we do anything, we need to understand the problem domain and all of its parts and relationships so that we can discover the whole story

Problem Domain: The specific area of analysis that encompasses every activity affecting or affected by a problem

It is very important to define the problem before trying to solve it

Sometimes teams jump into analysis without having defined the right problem or realizing they don't have the right data

Carefully defining a business problem is achieved through structured thinking

Structured Thinking: The process of recognizing the current problem or situation, organizing available information, revealing gaps and opportunities, and identifying the options Having a clear list of where you are expected to deliver, a timeline for major tasks and activities, and checkpoints so the team knows you're making progress.

A lot of spreadsheet functions have names that tell you what they do

Sum, Average, Count, Min, Max

Common Math functions for spreadsheets

Sum, Average, Count, Min, Max

Data analysts face the same challenges too. You might remember that data analysts aren't always given the complete picture at the start of a project. A big part of their job is to develop a structured approach and use critical thinking to find the best solution. Starts with understanding the problem domain

This is where structured thinking comes into play. To successfully solve a problem as a data analyst, you need to train your brain to think structurally. (Structured Thinking)

Data Range

When you click into your formula, the colored ranges let you see which cells are being used in your spreadsheet. There are different colors for each unique range in your formula. In a lot of spreadsheet applications, you can press the F2 (or Enter) key to highlight the range of data in the spreadsheet that is referenced in a formula. Click the cell with the formula, and then press the F2 (or Enter) key to highlight the data in your spreadsheet.

Context can turn raw data into meaningful information. It is very important for data analysts to contextualize their data. This means giving the data perspective by defining it. To do this, you need to identify:

Who: The person or organization that created, collected, and/or funded the data collection What: The things in the world that data could have an impact on Where: The origin of the data When: The time when the data was created or collected Why: The motivation behind the creation or collection How: The method used to create or collect it

Spreadsheet (Manage)

​​Manage different kinds of data with a spreadsheet. This can involve storing, organizing, filtering, and updating information. Spreadsheets also let you decide who can access the data, how the information is shared, and how to keep your data safe and secure.