1) What is the quickest way to add up a column of numbers ?
*) AutoSum - clicking on this command will add the SUM worksheet function below the current region
*) SUM - using this worksheet function manually.
*) AutoCalculate - highlight the cells and look in the status bar for the quick total.
2) What is the difference between the COUNT function and the COUNTA function ?
COUNT - Returns the number of non blank cells in a cell range.
COUNTA - Returns the number of non blank cells in a cell range, including logical values and text.
3) Using the NOW function inserts the current date and time. Is there a way to enter this information so it does not automatically update ?
Yes. Use these shortcut keys instead:
(Ctrl + ;) - inserts the current date
(Ctrl + Shift + ;) - inserts the current time
(Ctrl + ;) space (Ctrl + Shift + ;) - inserts the current date and time
4) What is the difference between the SUBSTITUTE function and the REPLACE function ?
SUBSTITUTE - returns the text string with a substring substituted for another substring.
REPLACE - this should be used when you want to replace text that occurs in a specific location in a text string.
SUBSTITUTE(text, old_text, new_text [,instance_num])
REPLACE(old_text, start_num, num_chars, new_text)
5) Are there any built-in worksheet functions that are volatile ?
Yes. TODAY, NOW, RAND, OFFSET, INDIRECT
CELL and INFO are also volatile but it depends on their arguments.
6) How would you sum all the values in column A where column B is greater than 15 ?
7) When would you use the VLOOKUP function ?
When you want to match a value in the first column of a table and return a specific item in that row.
8) What are the four arguments passed to the VLOOKUP function ?
VLOOKUP(lookup_value, table_array, colum_index [,range_lookup])
lookup value - the value to be found in the first column
table_array - the cell range containing the table of data
column_index - the column number for the value you want returned
range_lookup - (optional) whether to find an exact match
9) Can you describe some of the common problems that people experience when using the VLOOKUP function ?
This function defaults to approximate match (not exact match)
The value you are looking up must be in the first column of your data table.
The column index number will be broken is you insert or delete columns in your data table.
Forgetting to make the table_array absolute references (using $) before dragging down a formula.
10) What function would you use if you want to match a value in the first row of a table and return a specific item in that column ?
HLOOKUP - returns the value in a row that matches a value in the top row of a table.
11) When would you use a combination of the INDEX and MATCH functions ?
If you wanted to lookup a value that was not in the first column.
INDEX('col_return', MATCH('match_item', 'col_data',0), 1)
12) When would you use a combination of the OFFSET and MATCH functions ?
You should try not to use this combination because OFFSET is a volatile function and using it is very inefficient.
Always use INDEX and MATCH instead.
OFFSET('col_header', MATCH('col_data','table',0) -1, col_offset, 1, 1)
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext