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