Intermediate Spreadsheets Cheat Sheet

A Business › Business cheat sheet in English by patelivan

Data Analysis skills I learned in the Intermediate Spreadsheets course of DataCamp.

Spreadsheets

6,942 views 1,540 downloads 21 May 2021 History

PRs

Download

Knowing what's in a cell

ISTEXT()

ISNUMBER()

ISLOGICAL()

ISURL()

ISFORMULA()

ISDATE()

ISBLANK() - Can be used along with FILTER() to find all observations with a blank cell in a particular column.

An example:
After finding out which cells in the Athlete column are blank, you can use FILTER() on the whole dataset conditioned on whether or not the a cell in Athlete is blank. This will return all observations where the Athlete cell is blank.

Converting to a different type

N() - Convert a column's values to numbers.

TO_PERCENT()

TO_DOLLARS()

CONVERT(value, starting_unit, ending_unit) - To change the units of a number; from seconds to mins, or m/s to mph. Check the documentation to use the right unit abbreviation.

Working with numbers

LOG10()

LN()

EXP() - Raises Euler's number to a particular power

SQRT()

PI()

FLOOR(cell, 0.01) - Rounds down, in this case, to the next lowest hundredth. The default is down to the nearest whole number.

CEILING(cell, 0.01) - Rounds up, in this case to the next hundredth.

Simulation

RAND() - Generates random numbers between 0 and 1 from a continuous uniform distribution.

RANDBETWEEN(limit_1, limit_2) - Generates random integers between two limits from a discrete uniform distribution.

NORMINV(RAND(), 0, 1) - Generates random normal numbers from a normal distribution with mean 0 and std of 1.

*INV(RAND()) - Generates random numbers from other distributions. Look at the documentation for more.

Control Flow

NOT() - NOT(TRUE) is FALSE.

AND() - TRUE if all inputs are TRUE.

OR() - TRUE if any input is TRUE.

IF() - You can use it in conjunction with NOT(), AND(), and OR().

IFS(condition_1, "value if condition 1 is TRUE") - Test as many conditions as you like by taking pairs of arguments. If no condition is met, it will return N/A.

SWITCH(condition, category1, value1, category2, value2)

IF() or IFS() are used to convert logical values into categorical values. SWITCH() is useful for transforming categorical variables.

Blanks, Missing Values and Errors

NA() or IF(ISBLANK(cell), NA(), cell).

ISERROR().

ISERR() - Doesn't consider missing values to be errors.

#VALUE! - Nonsense data in a calculation.

#REF! - Reference cell that has been deleted.

#NUM! - Numbers being out of range.

#ERROR! - Syntax problem in a formula.

COUNTBLANK() - Count the number of blank cells in a range.

If you try to do any calculations with blanks, you'll get the wrong answer. Thus, just convert blank values to N/A values. Blanks are not errors but missing is.

Positional Matching

OFFSET() - Retrieves the values in cells offset from the current location by a certain number of rows and columns. It takes two arguments: the number of rows down to move from the current location, and the number of columns to move right. Positive column and row numbers mean it will more right and down respectively.

In OFFSET(), the height and width arguments return a range of cells.

Lookups & Matching

VLOOKUP(value you want to match, range of the dataset we want to find values in as absolute addresses, the number of the column that contains the values to be merged in, is_the_lookup_column_sorted?)

SORT(range of the data (not including the header row), column to sort by (numeric), TRUE to sort in ascending order)

VLOOKUP() allow you to merge or left join two datasets together. Thus, both datasets must have an ID column with some common values.

Badges

Downloaded from Unusual Locations × 1

Comments

No comments yet.