Timepoint Calculator Workbook (Excel File)

# IF Function: Basic Example (TRUE/FALSE)

**Function**

=IF(logical_test, value_if_true, value_if_false)

**3 Parameters**

- logical_test = Any value or expression that can be evaluated to TRUE or FALSE. (e.g. cell A1>10)
- value_if_true = The value (or formula) that you want to be returned (or calculated) if the logical_test argument evaluates to TRUE.
- value_if_false = The value (or formula) that you want to be returned (or calculated) if the logical_test argument evaluates to FALSE.

# More Keyboard, Less Mouse!

**From YouTube:** a great video tutorial by Mike “excelisfun” Girvin going over the keyboard shortcuts.

Okay, maybe it’s close to impossible to completely rid of the mouse, but I did notice that the **keyboard shortcuts are very underused**. Truth is that most don’t even know about these keyboard shortcut. Here are some **keyboard shortcuts that can dramatically speed up your productivity**,

**The Shortcuts**

- Ctrl + C = Copy
- Ctrl + V = Paste
- Ctrl + X = Cut
- Ctrl + F2 = Print Preview
- Excel 2003 Alt + F + U = Page Setup
- Excel 2007 Alt P + S + P = Page Setup
- Ctrl + Arrows keys = Move to end of current region
- Ctrl + Home = select cell A1
- Ctrl + Roller-ball-on-mouse = Zoom
- Atl + “=” = is the keyboard shortcut for Auto SUM
- Shift + Click selects range between to cells
- Ctrl + Shift + arrow = Select whole current region (This trick is the best Excl keyboard shortcut)

# Master the Arrow Keys!!

**Ctrl + ARROW KEY**

**Ctrl + SHIFT + ARROW KEY**

**The Tip:** implementing the Ctrl and Shft key in your artillery of keyboard shortcut can be one of the most useful and important ones to speed up productivity of your excel project.

**The Trick:** there are three different ways to utilize the arrow keys in Excel

*Arrow Keys (only)*: one key stroke moves the cursor in the direction of the Arrow Key.

*Holding Ctrl + Arrow Keys*: one key stroke on the arrow key while holding the “Ctrl” key will move the cursor to the last “non-blank” cell right before the next “blank” cell.

# VLOOKUP: Get a value from a different report!

**Function**

=VLOOKUP(lookup_value,table_array,col_index_num)

**3 Parameters**

lookup_value = the value to look up (e.g. reference number/text)

table_array = the source table to lookup

col_index_num = column number where the information is in

**CAVEATS**

**Lookup Value & Table Array
**The 1st column MUST be the column where the lookup value is. E.g.) If your lookup value is a site number, the 1st column of the Table Array MUST be a site number column

The format of the Lookup Value and the 1st column of the Table Array MUST be the same. Note: some numbers may be stored as text if the VLOOKUP function fails (“#N/A”)

VLOOKUP is a function that’s useful to get data from another report (or worksheet) into another. For instance, you may have a list of sites by their site numbers, but it does not have a country column that you want. In this case, you can use the VLOOKUP function to pull the country information from a different report (worksheet) to the report that you want.

# Index & Match Functions – A More Flexible VLOOKUP!!

**MATCH FUNCTION**

**INDEX FUNCTION**

**MATCH FUNCTION**

**Function**: a lookup function that returns the relative position of an item in a list

=MATCH(lookup_value,lookup_array,match_type)

**Parameters**: 3

- lookup_value = the value you use to find the value you want in a table.
- lookup_array = contiguous range of cells containing possible lookup values.
- match_type = tells index which row to look at.

0 = Exact Match

1 = Approximate Match (less than the lookup_value)

-1 = Approximate Match (greater than the lookup_value)

**INDEX FUNCTION**

**Function**: lookup function that can do a two-way lookup.

=INDEX(array,row_num,column_num)

**Parameters**: 3

- array = the two dementioanl table
- row_num= tells index which row to look at
- column_num = tells index which row to look at