Dear Analyst

I discuss how to calculate percentiles in Excel or Google Sheets using the PERCENTILE function. With the PERCENTILE function, you can calculate the value that would represent nth percentile in your list of values. This is not exactly the calculation I was looking for. Instead, what if you wanted to know what the rank percentile is (as a percentage) for a given value in your data set? See the image below from this Google Sheet to see the difference between these two scenarios:

Percentile as a value vs. percentile as a %

It's a really small difference, but I simply wanted to calculate which percentile a given value fell in given my data set. In the Google Sheet, you'll see a list of test scores. Is Dan's test score in the 30th percentile? 50th? There's no built in function to find this.

If you know the percentile, it's easy to calculate the value that the percentile represents with this formula:

[crayon-5ef070ac89dd8791400083/]

data is simply your list of values and percentile is anything between 0 and 1. To answer my original question, you need to use a combination of RANK() and the COUNT() functions:

[crayon-5ef070ac89de0187385226/]

In the Google Sheet, the full formula for cell D15 is:

[crayon-5ef070ac89de2812996426/]

The reason you need the 3rd argument set at 1 for the RANK.EQ function is so that you reverse the list of values that you are ranking (the greatest value in your list ends up being ranked last). This way, when you divide the result of the RANK.EQ function by the result of the COUNT function, you'll get the actual percentage of the percentile.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* This Week In Startups: Episode #1002 - Wealthfront CEO & Co-founder Andy Rachleff on hitting escape velocity at Wealthfront, pioneering “self-driving money” for consumers, shares insights on product-building in 1980’s vs. today, best investor attributes, & the future of NextGen banking

In this episode, I discuss how to calculate trends over time in Excel for the purposes for forecasting future values. I reference an old post about calculating trends where someone recently left a comment about the counter-intuitiveness on calculating averages of changes in your values. In order to follow along with this episode, I would recommend looking at the screenshot of Excel in this episode/blog post.

Is No-Code the future?

One of the other podcast episodes I reference in this episode is about the no-code movement. Everyone in my field is getting really jazzed about the possibilities of no-code platforms and the abilities it gives to millions of people who don't know how to code (myself included). I discuss how I never considered myself as someone who is part of the "no-code" movement since I just figured I was building stuff in Excel and Google Sheets.

Alex Hudson wrote a great post about the "delusion" of no-code which provides the other side of the argument. I agree with a lot of his points and I think it's worth a read if you consider yourself more than an analyst, but also a builder or maker who is building tools for your team or company. These two paragraphs stood out to me, but I would argue that it is indeed possible to have production-ready systems built with no-code tools:

With code, we’re used to being able to create a change, deploy that into a separate environment to test a change, and then be able to deploy into production. Or, failing that, we can deploy into production but switch the feature on progressively, so that if there are errors we can pick them up quickly and resolve them without impacting all our users.With “no code”, it tends to be difficult or impossible to have a non-production environment. Even if you do have one, accurately copying changes over from one to the other is non-trivial. Salesforce has some excellent tooling available to make this work, and even in that environment it’s extremely difficult to do.The 'No Code' Delusion - Alex Hudson

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* Software Engineering Daily: Indie Hackers (3 Years Later) with Courtland Allen* Product Hunt Radio: Early days at Facebook and advice for pitching VCs with Mike Vernal

If you are by your computer, you may want to open this Google Sheet to understand the example discussed in this episode. I walk through a rather long formula involving the FREQUENCY(), COLUMN(), MAX(), and the ARRAYFORMULA() functions in Google Sheets.

Here's the full formula below to calculate win streaks in the Google Sheet:

[crayon-5ed4c0d899e96733544033/]

You can see an embed of the Google Sheet below as well:

Calculating win streaks with bye weeks

What made this problem interesting is that if there are Xs in the weeks, those are considered bye weeks for the players and shouldn't count against the player. In the Google Sheet, you'll notice that John has a X for Week 3, but has Ws for Weeks 1, 2, 4, and 5. Just because he had a bye week for Week 3 means he still has a 4-game winning streak for those 5 weeks.

This question originally appeared on the Mr. Excel forum here. The answer involves (in my opinion) a non-traditional use of the FREQUENCY() function to find the number of Ws for a given player that fall between different weeks where the player took an L.

Array-entered formulas

For those not familiar with the FREQUENCY() function, it's one of the special functions in Excel that must be array-entered into Excel. Traditionally, this means while you're editing the formula, you hit CTRL+SHIFT+ENTER to array-enter the formula. In newer versions of Excel, you don't have do the CSE combination as Excel just knows to apply the formula to an array of cells.

CSE doesn't exist in Google Sheets, so in order to array-enter formulas, you just use the ARRAYFORMULA() function to tell Google Sheets this formula should be applied to a range of cells.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* Freakonomics: Episode #391: America's Math Curriculum Doesn't Add Up

This is my talk from Webflow’s No-Code Conference that took place on November 13th, 2019 in San Francisco. The title of my talk was called Building No-Code Tools and Applications from Spreadsheets. The slides from my presentation are on SlideShare here.

Themes from the talk

This was my first time talking about my experience with spreadsheets and the Excel community in a public forum. The three themes I discussed based on my experience using spreadsheets for the last 10+ years are:

* Whatever platform you choose to build or make on, the platform should fundamentally challenge your beliefs about what it means to build apps and tools* The technology behind many no-code platforms (including spreadsheets) is not as revolutionary as it may seem* The community of users behind the platform will always be more important than the platform itself

The talk and slides should be published in the next few weeks, but I published my slides already which you can view below:

Building No-Code Tools and Applications from Spreadsheets from Al Chen

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* Future of Coding: Episode #43: Unveiling Dark – Ellen Chisa & Paul Biggar* Real Time with Bill Maher: Episode #509: Salman Rushdie, Gina McCarthy, Barney Frank, Linette Lopez, Noah Rothman

A few weeks ago I had the opportunity to teach a week-long data analytics course through General Assembly. The course was taught entirely online using Zoom. I discuss some of the topics the students learned in the class, and what the experience was like teaching an online class in real time. The topics we covered included Excel, SQL, and Tableau. We also discussed topics relating to data narratives and telling data-driven stories.

Source: General Assembly

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* Software Engineering Daily: Talking Python with Michael Kennedy* Data Engineering Podcast: Episode #98: Navigating Boundless Data Streams With the Swim Kernel* Hanselminutes: Episode #702: On Being Productive with Cantilever’s Ty Fujimura

A bit of a click-baity title, but I wanted to talk through some of the signs you know you’re using too much Excel. If you are worried about a friend who is using too much Excel, ask him or her if they recognize any of these signs. This is an episode based on a blog post I wrote in 2017 on the 10 signs you’re using too much Excel, and I just wanted to focus on the top 5 from that list.

Teaching data analytics

I haven’t posted an episode in a few weeks because I’m preparing to teach a remote course on data analytics through General Assembly. It’s my first time teaching weeklong course about Excel, SQL, and Tableau 100% remotely using Zoom. If you’re interested in learning more about the roucse (and hearing from yours truly), check out the course syllabus here. Once the course is over, I’ll probably do a recap on my experience teaching this course on Dear Analyst.

Source: General Assembly

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* EnterpriseReady: Episode #5 – The Evolution of Enterprise with Edith Harbaugh of LaunchDarkly* ShopTalk Show: Episode #374 – VisBug with Adam Argyle* Data Engineering Podcast: Episode #95 – Building Tools and Platforms for Data Analytics

Range: Why Generalists Triumph In A Specialized World by David Epstein came on my radar after listening to another podcast. Thought the premise was interesting in that people who “dabble” in various interests and hobbies end up excelling (no pun intended) in life and have a more rewarding career. I read a few excerpts that show what this book is all about, and how this applies to my journey becoming proficient in Excel and other tools.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* Changelog: Episode 353 – The war for soul of open source

New Excel feature alert! Probably one of the biggest announcements in a long time from the Excel world: a new function to supplant VLOOKUP and HLOOKUP. What is it you ask? The XLOOKUP function.

Summary of XLOOKUP benefits

I discuss some of the benefits, implications for new and existing users of Excel, and also some of the great comments in the announcement blog post. according to the blog post, VLOOKUP is the 3rd most used function in Excel behind SUM and AVERAGE!

Read more from the official Microsoft Excel announcement. The one and only Bill Jelen of MrExcel did a great writeup of the new XLOOKUP function. In short, here are some of my favorite improvements XLOOKUP brings to the traditional VLOOKUP and INDEX/MATCH functions for finding your data:

* Defaults to exact match on the lookup (no more adding false or 0 in your VLOOKUP formulas)* Can insert columns into the range of data where you are doing the lookup without needing to change the column number of the data you want to return* Can look to the left of the range (this is already doable with INDEX/MATCH, however)* Performance improvements due to the fact you only need to select two columns instead of the whole range for the lookup

XLOOKUP in action

See the XLOOKUP function in action (from the blog post):

What people are saying

Some of my favorite comments from around the web on the new XLOOKUP function:

Today I discovered that #XLOOKUP is coming to Excel to effectively replace the VLOOKUP function. My life is complete. I will die a happy man.— Jayesh Navin Shah (@jayeshnavin) August 31, 2019

#Excel is probably one of the first software that anyone learns when they start to look at the numbers. Apparently, the widely used #VLOOKUP function will have an heir to overshadow its glory: #XLOOKUP. https://t.co/7P4rBYca5t— Cem Tekesin (@TekesinCem) August 30, 2019

What a great enhancement. This will be much easier to be taught to beginners – I’m SO looking forward to be able to use and teach this new function! Thank you very much for considering the suggestion.Katharina_Schwarzer (blog post comment)

I forgot to offer my congratulations to the development team in my earlier post.  For me, traditional Excel appears plagued by ‘cheap and cheerful’ techniques that provide partial solutions but are constructed so as ‘not to place demands upon the end-user’.  All of the new dynamic array functions seem to have brought with them a noticeable greater degree of rigour.  More thought appears to have gone into the planning, so all the use cases that one might reasonably expect seem to be catered for.  Ultimately, this reduces the need for the ‘tips and tricks’ that provide workarounds or guidance where there is overlapping functionality.  Great stuff!

A few months ago, The New York Times’ Digital Transition team headed by Lindsey Cook published a blog post about a data bootcamp they led for their reporters and editors. The New York Times frequently publishes amazing data visualizations, and it’s awesome to see this newsroom pushing their reporters into the world of “data journalism.” In the words of Cook:

We wanted to give our reporters the tools and support necessary to incorporate data into their everyday beat reporting, not just in big and ambitious projects.

In this episode, I review some of the materials (Google Drive link) Cook and her team released in the blog post on skills taught during this bootcamp. The entire bootcamp is done in Google Sheets, and some of the skills they teach include importing data, Pivot Tables, cleaning data, and more.

Telling a Data-Driven Story

What I appreciate most about this bootcamp (based on the materials provided) is the focus on using data to tell a story (plug: I have an online class about this subject). It’s one thing to report on the facts, but another to use the data to support the facts. The bootcamp goes into detail on different method reporters can use to present data in a compelling way to their audience. This Google Doc shows some of the methods you can use to create a data-driven story. The graphic below is also a good “cheat sheet” on methods you can use.

The argument for data journalism is shown in these 5 stories from reporters who started using spreadsheets in their stories. In this episode, I highlight the story from John Ismay, an At War reporter who used spreadsheets for the simple purpose of keeping track of characters and event timelines.

Props to the New York Times for releasing these materials for free to the public so that more newsrooms can train their staff to write more data-driven stories!

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* a16z: The History and Future of Machine Learning with Professor Tom Mitchell* SyntaxFM: Episode 155: Hasty Treat – Making Yourself Uncomfortable to Grow

I read a few excerpts from Superforecasting: The Art and Science of Prediction by Philip E. Tetlock and Dan Gardner. What does it mean to be a good forecaster? How can we take lessons from the book to be better modelers? I just started reading the book but already have learned so much about forecasting, and sharing some of my favorite parts of the book so far.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* The Remote Show: Episode 11 – Rebecca Corliss, VP of Marketing at Owl Labs* SyntaxFM: Episode 149 – Hasty Treat – Workshops