# 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!*

`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`

## Leave a Reply