Excel for Analytics

Here I focus on a few lesser-known but highly valuable Microsoft Excel functions that MBA students can utilize to enhance their data collection, analysis, calculation, simulation and financial modeling tasks.

1. XLOOKUP Function

• Arguments: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Explanation: Replaces the traditional VLOOKUP, allowing for more flexible and powerful lookups.
• Example: =XLOOKUP("Apple", A1:A5, B1:B5, "Not Found") - Searches for "Apple" in A1:A5 and returns the corresponding value from B1:B5.

2. TEXTJOIN Function

• Arguments: TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
• Explanation: Combines multiple strings with a specified delimiter, with an option to ignore empty cells.
• Example: =TEXTJOIN(",", TRUE, A1:A3) - Joins the text in cells A1 to A3 with a comma, ignoring any empty cells.

3. FORECAST.ETS Function

• Arguments: FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
• Explanation: Forecasts future values based on historical data using the Exponential Triple Smoothing (ETS) algorithm.
• Example: =FORECAST.ETS("01/01/2023", B1:B10, A1:A10) - Forecasts the value for the date 01/01/2023 based on the historical data in A1:A10 and B1:B10.

4. AGGREGATE Function

• Arguments: AGGREGATE(function_num, options, ref1, [ref2], ...)
• Explanation: Applies various aggregate functions like SUM, AVERAGE, etc., with options to ignore hidden rows, error values, etc.
• Example: =AGGREGATE(14, 6, A1:A10) - Returns the median of the range A1:A10, ignoring hidden rows and error values.

5. SWITCH Function

• Arguments: SWITCH(expression, value1, result1, [default_or_value2, result2], ..., [default])
• Explanation: Evaluates an expression against multiple values and returns the corresponding result.
• Example: =SWITCH(A1, "Red", "Stop", "Green", "Go", "Wait") - Returns "Stop" if A1 is "Red," "Go" if A1 is "Green," and "Wait" otherwise.

6. IFS Function

• Arguments: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
• Explanation: Evaluates multiple logical tests and returns the value corresponding to the first TRUE test.
• Example: =IFS(A1>100, "High", A1>50, "Medium", "Low") - Returns "High" if A1 is greater than 100, "Medium" if A1 is greater than 50, and "Low" otherwise.

7. CONCAT Function

• Arguments: CONCAT(text1, [text2, ...])
• Explanation: Combines multiple strings into one without any delimiter.
• Example: =CONCAT(A1, B1, C1) - Concatenates the text in cells A1, B1, and C1.

8. UNIQUE Function

• Arguments: UNIQUE(array, [by_col], [exactly_once])
• Explanation: Returns the unique values from a range or array.
• Example: =UNIQUE(A1:A10) - Returns the unique values from the range A1:A10.

9. RANDARRAY Function

• Arguments: RANDARRAY([rows], [columns], [min], [max], [integer])
• Explanation: Generates an array of random numbers with specified dimensions, range, and type.
• Example: =RANDARRAY(5, 2, 1, 10, TRUE) - Generates a 5x2 array of random integers between 1 and 10.

10. LET Function

• Arguments: LET(name1, value1, [name2, value2, ...], calculation)
• Explanation: Assigns names to calculation results within a formula, making complex formulas more readable.
• Example: =LET(x, A1, y, B1, x*y) - Assigns A1 to x and B1 to y, then returns the product of x and y.

Use cases

To sum up:

• These functions can significantly enhance the efficiency and effectiveness of data analysis and financial modeling tasks for MBA students.
• By integrating these into their workflows, students can gain deeper insights, automate complex calculations, and develop more robust models.