by ·0 Comments
Excel Analysis Toolpak

Running Statistical Data Analysis (EDA) using Excel Functions and Formulas.

Back in August Microsoft made a momentous announcement of good ole’ Excel now supporting Python scripts (at least for those Office 365 subscribers who are part of the Beta Insider channel.) While this functionality brings an array of virtually unlimited possibilities to an already powerful tool, many analysts recognize that support for various statistical analysis techniques is one of the most promising areas to capitalize here. Having said that, some users do not realize that they don’t need to leverage sophisticated Python functions to conduct exploratory data analysis of their datasets – they can instead install Excel add-in called “Analysis Toolpak” to get them started on their statistical learning journey. In this post we will use this tool to analyze weight distribution of players appearing on the 2023/24 Chicago Bears Roster. These summary statistics will help us get a quick overview of this dataset, and in turn, make it easier for us to spot patterns and outliers. We will then recreate these capabilities via Excel functions and formulas.

Continue reading

by ·0 Comments
Array Functions in MS Excel

Working with MS Excel Array Functions (FILTER, RANDARRAY, SEQUENCE, UNIQUE, SORT, SORTBY)

It’s hard to keep up with seemingly non-stop additions to Excel’s impressive catalog of functions. If you happen to belong to Office Beta channel you might have started working with the newest release of text and array functions. As a regular Office 365 subscriber I’ve already covered newish XLOOKUP and LAMBDA functions on this blog. It was last year when the Excel team introduced dynamic array functions, also known as spilled array functions. These functions return arrays of of values of different sizes and spill them into adjacent cells. You can usually specify how long and how wide the output ranges should be. These functions include – UNIQUE (returns unique values from the range of values), SEQUENCE (retrieves a sequence of values), SORT, SORTBY, RANDARRAY(array of random numbers based on specified parameters), and FILTER. In this post we will explore examples on how to use these functions.

Continue reading

by ·0 Comments
LAMBDA Function - MS Excel - MPG

Having fun with Excel LAMBDA function – building a BMI calculator and MPG conversion formulas

Let’s face it, despite the ever-growing catalog of 500(!) or so Excel functions, we cannot expect Microsoft developers to create all functions that meet the needs of every Excel user. Even if this feat were possible, the program itself would become unusable due to the sheer number of functions made available blocking us from using a few that we actually need. Traditional solution is age-old – using VBA to code a User Defined Function (UDF.) In fact, we’ve already covered the process of creating UDFs here and here.

Fast forward to 2016, when Excel teased the idea of the LAMBDA function, initially limiting its release to Office 365 subscribers opted in into the Office Insider edition. Since then this “ultimate” Excel function went through a number of revisions and enhancements and while it didn’t make its wider debut in time for Excel 2021 version, it was eventually released to the general public (i.e. all current Office 365 subscribers) earlier this month. We can now create a custom Excel function without having to deal with the Visual Basic Editor window ever again.

Continue reading

by ·6 Comments
XLOOKUP Function

I personally don’t know why it took this long for Microsoft Excel team to create XLOOKUP function. The fact that VLOOKUP is considered to be one of Excel’s most widely used functions reflects a strong demand in string look up tabulations. Surely, a multitude of VLOOKUP‘s limitations can be overcome with patience, helper columns, INDEX/MATCH, CHOOSE, OFFSET and other constructs. Yet, why would we use any workarounds, when we would rather utilize a more powerful function with multitude of applications? Meet, much anticipated XLOOKUP function, which was officially released to Office 365 subscribers in early February of this year. It offers a really long list of additional benefits; in today’s tutorial we will review 11 scenarios that take full advantage of the following XLOOKUP features:

  • LEFT lookup
  • Horizontal lookup
  • Multi-cell/array retrieval
  • Match based on wildcard conditions
  • Combination of Vertical AND Horizontal lookups
  • Lookup based on multiple criteria
  • Lookup in reverse order
  • Lookup for maximum/minimum values
  • Built-in Error Handling
  • Exact match by default
  • Flexible approximate match

Continue reading