Study Notes #6

=FIND and =LEFT

=FIND(" ", A2)
=LEFT(A2, B2-1)

=LEFT(A2, FIND(" ",A2)-1

FIND and LEFT can be used to extract text. FIND can be given a substring and a cell to return the position in a string where the substring was found. LEFT can then be used to extract a certain number of characters from a cell, starting from the left side.

RIGHT therefore extracts from the right side, while MID can extract from some starting point in the middle of a cell.

=left(A2, FIND(" ", A2)-1)

hello world

The first space on the left less 1 character (space).

Pro Tip: To display the formula of one cell in another, use the FORMULATEXT function

=MID(text, start_num, num_chars)
text     
         text string containing the characters you want to extract
start_num    
         position of the first character you want to extract in text
num_chars
         the number of characters you want MID to return from text

=FIND(find_text, within_text, [start_num])
find_text    
         the text you want to find
within_text   
         the text containing the text you want to find
start_num    
         OPTIONAL - character position at which to start the search

Quiz Answer Key

I need to study this more!

View File Here

CONCATENATE will join together two or more strings. It’s important to note that this will not automatically add spaces between them, so make sure to add spaces as formula parameters if you need them.

TRIM will help to remove excess whitespace from a string.

PROPER sets the first letter of each word to upper case, with the rest lowercase.

UPPER sets all letters to upper case, while LOWER sets all letters to lowercase.

• + for addition
• - for subtraction
• * for multiplication
• / for division

There are also the functions SUM and AVERAGE, which behave as their names suggest – summing or averaging two or more cells, numbers or a range of cells.

Clean Data – free of corrupt or inaccurate data items.

---

Data Analization

Aggregation Functions

A function that operates across a group of data, resulting in a single value. Examples include SUM, AVERAGE, MAX, MIN, MEDIAN and STDEV, although there are many more

=IF

• return different values based on whether a condition is true or false. The first parameter is the condition, the second is what the cell value should be if the condition is true, and the optional third parameter is the cell value if the condition is false (skipping the third parameter will otherwise just show “FALSE” in the cell).

Example:

=IF(B3="Canada", "Canada", "NA")

If the data in B3 is "Canada", the cell will return the text "Canada", otherwise "NA".

=IF(condition, value if true, [value if false])

Comparison Operator

• > for greater than
• < for less than
• = for equal
• >= for greater than or equal to
• <= for less than or equal to
• <> for not equal (note that are no equal signs!)

=AND

Example: 

=IF(AND(A10="Amol", B10="Iran"), "You got the AND", "T")

=OR

Example: 

=IF(OR(A11="Karema", B11="Iran"), "You got the OR", "T")

AND is another useful logical function, which can combine more than one condition. If used with an IF statement, note that it is nested within the IF function, such as:

IF(AND({condition1}, {condition2}), ...)

AND requires that all conditions are true to be true. There is also the OR function, which just needs one of any of its conditions to be true for it to be true itself. Lastly, there is also the NOT function, which reverses a true to false or a false to true.

Conditional Aggregation Function

A function that operates across a group of data with logical conditions. COUNTIF and SUMIF are two examples of such functions.

COUNTIF will count the number of cells in a range that meet a given condition.

Likewise, SUMIF will instead sum the values from the cells in a range that meet a given condition.NEXT

Pivot Table

Pivot tables sum and aggregate in a single step. In Excel, if you select all the relevant data, you can use Insert -> Pivot Table (in Google Sheets pivot tables appear under the Data tab instead). You then can click on the desired fields to include in the pivot table, or drag them to the relevant area (such as column or row) you want to use the field within.

In the example in the video, we first chose to make the teams the rows and the positions the columns of the pivot table, with the names as the values. This defaulted to a count of names. After switching to salaries, we switched the aggregation to the sum of the salaries instead.

Lookup Function

• function that uses a keyword and index to “look up” a value in a table
• There are both horizontal and vertical lookup functions, although we will focus on a vertical one called VLOOKUP