Show Menu
Cheatography
  1. Home >
  2. Business and Marketing >

Intermediate Spreadsheets Cheat Sheet by

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

Knowing what's in a cell

ISTEXT()
ISNUMBER()
ISLOGI­CAL()
ISURL()
ISFORM­ULA()
ISDATE()
ISBLANK() - Can be used along with FILTER() to find all observ­ations 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 condit­ioned on whether or not the a cell in Athlete is blank. This will return all observ­ations where the Athlete cell is blank.

Converting to a different type

N() - Convert a column's values to numbers.
TO_PER­CENT()
TO_DOL­LARS()
CONVER­T(v­alue, starti­ng_­unit, ending­_unit) - To change the units of a number; from seconds to mins, or m/s to mph. Check the docume­ntation to use the right unit abbrev­iation.
 

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.
CEILIN­G(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 distri­bution.
RANDBE­TWE­EN(­lim­it_1, limit_2) - Generates random integers between two limits from a discrete uniform distri­bution.
NORMIN­V(R­AND(), 0, 1) - Generates random normal numbers from a normal distri­bution with mean 0 and std of 1.
*INV(R­AND()) - Generates random numbers from other distri­but­ions. Look at the docume­ntation 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 conjun­ction with NOT(), AND(), and OR().
IFS(co­ndi­tion_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­(co­ndi­tion, category1, value1, category2, value2)
IF() or IFS() are used to convert logical values into catego­rical values. SWITCH() is useful for transf­orming catego­rical variables.

Blanks, Missing Values and Errors

NA() or IF(ISB­LAN­K(c­ell), NA(), cell).
ISERROR().
ISERR() - Doesn't consider missing values to be errors.
#VALUE! - Nonsense data in a calcul­ation.
#REF! - Reference cell that has been deleted.
#NUM! - Numbers being out of range.
#ERROR! - Syntax problem in a formula.
COUNTB­LANK() - Count the number of blank cells in a range.
If you try to do any calcul­ations 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 respec­tively.
In OFFSET(), the height and width arguments return a range of cells.

Lookups & Matching

VLOOKU­P(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­_lo­oku­p_c­olu­mn_­sor­ted?)
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.
 

Metadata

  • Languages:
  • Published: 21st May, 2021

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          More Cheat Sheets by patelivan

          Supervised Learning in R: Regression Cheat Sheet
          Introductory Statistics in R Cheat Sheet
          Introduction to Regression in R Cheat Sheet