Advanced Excel
108 flashcards by MentisLibrary
Spreadsheet analysis beyond the basics: lookups, conditional aggregation, text and date functions, absolute references and Tables, pivot tables, data tools, dynamic array formulas, and reading Excel error values.
- What are the arguments of XLOOKUP?
- XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
- Why is XLOOKUP preferred over VLOOKUP?
- It defaults to an exact match, can return a column to the LEFT of the key, and survives inserted columns because it takes a range rather than a column number.
- What are the arguments of VLOOKUP?
- VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), searching the FIRST column of the table.
- Why can VLOOKUP not return a value to the left of the key?
- Because col_index_num counts columns to the RIGHT from the lookup column, which is always the first column of the table array.
- How does INDEX combined with MATCH work?
- MATCH finds the position of the key and INDEX returns the value at that position in another range.
- What does the third argument of MATCH control?
- The match type: 0 is exact, 1 is the largest value less than or equal to the key in ascending data, -1 is the smallest value greater than or equal in descending data.
- How do you look up a value in a table by both row and column?
- Nest two MATCH calls inside INDEX.
- When is an approximate-match lookup the right choice?
- When you are mapping a number into a band, such as a tax bracket or a discount tier.
- How do you show a friendly message instead of #N/A?
- Pass the fourth argument of XLOOKUP, or wrap an older lookup in IFNA.
- What does CHOOSE do?
- Returns the nth item from a list of values, using a number as the selector.
- What does INDIRECT do, and what does it cost?
- It turns a text string into a real cell reference, and it is volatile, so it recalculates on every workbook change.
- What does OFFSET return?
- A range shifted from a starting reference by a number of rows and columns, optionally resized.
- What are the three arguments of IF?
- IF(logical_test, value_if_true, value_if_false).
- When should a nested IF become IFS?
- As soon as there are more than two or three conditions, since IFS lists condition and result pairs without nesting.
- What does SWITCH do that IFS does not?
- It compares ONE expression against a list of exact values, so the expression is written once.
- How do you test several conditions at once?
- Wrap them in AND for all-must-hold or OR for any-may-hold.
- What does NOT do in a formula?
- Reverses a logical value, turning TRUE into FALSE and back.
- What does IFERROR do?
- Returns an alternative value when the wrapped formula evaluates to ANY error.
- IFNA versus IFERROR
- IFNA catches only #N/A, the error a failed lookup produces. IFERROR catches every error type.
- What does a comparison such as =A2>10 return?
- The logical value TRUE or FALSE, which Excel treats as 1 and 0 in arithmetic.
- What are the arguments of SUMIF?
- SUMIF(range, criteria, [sum_range]): the range that is tested, the test, then the range that is summed.
- Why does SUMIFS take its arguments in a different order from SUMIF?
- SUMIFS(sum_range, criteria_range1, criteria1, ...) puts the summed range FIRST because the criteria pairs are open ended.
- How do you count rows meeting one condition?
- COUNTIF(range, criteria).
- How do you count rows meeting several conditions?
- COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...).
- How do you average only the rows that match a rule?
- Use AVERAGEIFS(average_range, criteria_range1, criteria1, ...).
- Which functions give the largest value meeting a condition?
- MAXIFS and MINIFS, with the same argument order as SUMIFS.
- What makes SUBTOTAL different from SUM?
- SUBTOTAL ignores rows hidden by a filter, and its 101 to 111 function numbers also ignore manually hidden rows.
- What can AGGREGATE do that SUBTOTAL cannot?
- Its second argument can ignore error values and hidden rows, and it offers LARGE, SMALL and PERCENTILE as well as the basic aggregations.
- What does SUMPRODUCT do?
- Multiplies corresponding elements of arrays and sums the products.
- COUNT versus COUNTA versus COUNTBLANK
- COUNT counts numbers, COUNTA counts every non-empty cell, and COUNTBLANK counts empty ones.