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
 

A beginner’s Guide to BigQuery Sandbox and exploring public datasets.

A beginner's guide to BigQuery Sandbox

          As you might realize by now, writing SQL queries is one of the essential skills any inspiring data analyst needs to master. After all, larger datasets are typically stored in relational databases and Structured Query Language is the language that helps us communicate with such databases. Sure, NoSQL is gaining prominence amid the growing popularity of nontraditional databases, but we need to learn to crawl before we start walking. Merely 10 years ago, you would need to download and install a RDBMS software package (be it MySQL, PostgreSQL, or SQLite), load a sample database and do a hundred pushups before you could write your very first SQL query. Luckily technology sprung ahead and we now have a plethora of web-based SQL editor options from SQL Lite Online to SQL Fiddle that eliminate the software setup step, but might still require us to load sample data. What if you wanted to access real-world big data sets from the comfort of your browser without having to download any software, no hassle, no trial, no credit card required? Well, you’re in luck, what follows is the beginner’s guide to Google BigQuery’s Sandbox. An active Google account is your cost of admission. BONUS: Machine Learning models are powered by nothing else but SQL are also included.
Continue reading

by ·0 Comments

Highlights of Gary Cokins Business Analytics seminar

Business Analytics

         Last month I attened Business Analytics seminar by “an internationally recognized expert, speaker, and author in performance improvement systems and cost management” : Gary Cokins.

         My slideshare presentation in the bottom of this post summarizes Gary’s informative and thought-provoiking presentation. In fact, some statements in this post were provoked by this seminar. Gary started by quoting Jeanne X. Harris :“40% of important decisions are not based on facts but rather on intuition, experience, and anecdotal evidence.” Arguably, we could improve the world by basing our decisions on facts, and making better decisions in the process. In addition, better decisions result in better actions, saving us time, effort, money, and other resources. The “actions” part is really important in terms of differentiating Business Analytics from Business Intelligence.

Continue reading