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 ·0 Comments
Table Joins

Understanding Table Joins in SQL

Working with data often involves the need to utilize multiple data sources, usually stored in different data tables (in case of database storage) or data frames (when it comes to programming languages or data visualization tools.)  In order to put power of this data to a good use we want to be able to join these tables based on a field or fields they have in common (foreign key[s]) or sometimes values in the field that are different. Not only basic principles of table joins – INNER, OUTER (FULL, LEFT, and RIGHT), CROSS (or Cartesian) or even UNION-ing tables are universal to most relational databases and flavors of SQL, they also apply to working with data frames. In this post we will explore examples of using these table joins in a PostgreSQL database, while adding SELF, and LEFT/RIGHT exclusive joins for a good measure.

Continue reading

by ·0 Comments
US COVID-19 Cases

During these uncertain times, how can you make sense of the data tsunami being presented on the state of pandemic in US? For the last couple of months, many Americans found themselves checking the spread of COVID-19 cases on a daily basis. As most of US states went into shelter-in-place mode, resources like Johns Hopkins and 91-DIVOC became a daily refuge for those seeking to stay informed. In today’s post, we will work on creating our own version of a web-based, interactive and visually appealing COVID-19 dashboard using Google DataStudio. Doing so we will gain a better understanding of the data used, decide on the type of data we deem most relevant, and maintain control over the best ways to visualize such data to help our audience make most sense of it. In the process of building this data viz, we will utilize various objects and features of the mighty GDS application: Google Sheets connector, Calculated fields, Scorecard, Table, Geo Map, Line and Combo charts, Date range, Filter controls and recently released optional metrics – are some but not all features we will cover.

Continue reading