They languish behind the unused “Fx” button on the formula bar. When you use them for the first time you aren’t quite sure how to use them again. However, once you really start using them you don’t understand how you did certain tasks before. The following five functions will soon be all over your sheets:
FORECAST: no this does not return the weather. It actually performs a full linear regression on referenced ranges, takes in a range of independent and dependent variables, takes in a new independent value, and forecasts the dependent variable. Simply put, if you have data that is explanatory you can use it to forecast a value based on the regression of that data. REAL WORLD USE: interpolating interest rates can be done by regressing current rates against the tenors and providing the desired tenor.
SUMPRODUCT: this one is easy to understand, but tough to master. At its most basic level it multiplies corresponding values from two ranges and sums up all the products, however it can be implemented to using binary flags to include certain values in a calculation. REAL WORLD USE: calculating weighted averages are done by using SUMPRODUCT (weights, values)/sum(weights).
OFFSET: we can’t really say enough about this function. Moving data is critical for analyses. This function lets you reference cells by fixing on one cell and using numerical coordinates to return the contents of another cell. It’s not always obvious, but this let’s you reorganize data however you like and can also be combined with other functions for powerful lookups. REAL WORLD USE: combine with MATCH for a more powerful version of HLOOKUP or VLOOKUP.
MATCH: an extraordinarily simple function that looks up a value from a list and returns it’s numerical position in the list. It can be used to match exact values or find numerical values that are close to a lookup value. REAL WORLD USE: We often find ourselves checking whether a value exists within a long column of numbers. This function lets you quickly determine if those values exist in a list.
SUMIFS: many users are used to SUMIF, where you can sum values based on a specific condition. However, SUMIFS allows you to designate multiple conditions. REAL WORLD USE: if you are trying to sum values to fit into a range of two columns, SUMIFS can quickly set it up.
Test your skills at: Enstructcorp.com/demo/
