You blink a few times at the screen and realize what you’re seeing is not a type. $6.2B has left your bank due to some rogue trader making untimely bets on the market. That’s B as in billion. You call up the modeler who was supposed to make sure this never happens to your bank. […]
The post Dear Analyst #38: Breaking down an Excel error that led to a $6.2B loss at JPMorgan Chase appeared first on .
In Excel or Google Sheets, text manipulation is usually associated with data cleaning, data cleansing, and data transformation. Sometimes your data is “dirty” and needs to be categorized in a different way or you need to “extract” a piece of text from a another piece of text. In this example, we use a combination of […]
The post Dear Analyst #37: Text manipulation functions to extract domain names from email addresses appeared first on .
On a recent episode of The Intelligence, The data editor at The Economist spoke about a U.S. presidential election forecast their publication is working on. I looked more into their model and discuss some of the features and parameters of their model and what makes their forecast unique. Some of the techniques used in The […]
The post Dear Analyst #36: What The Economist’s model for the 2020 presidential election can teach us about forecasting appeared first on .
Have you ever wondered what the underlying meaning of your dreams are? Chances are you may have tried Googling something like “What does it mean to dream about [INSERT DREAM].” In The Shape of Dreams, Federica Fragapane answers this very question of what people around the world dream about by using Google Search queries from […]
The post Dear Analyst #35: Analyzing what people dream about with the Shape of Dreams data visualization appeared first on .
This is one of my favorite VLOOKUP tips. Given that it’s pride month, we’ll be applying this tip to a list of all pride events in the United States. Here is the Google Sheet if you want to follow along with this example. Here’s the scenario: you have a super large table in Excel or […]
The post Dear Analyst #34: Trick for finding column index for VLOOKUPs using pride events data appeared first on .
The Black Lives Matter movement has come to the forefront in the news media. People around the world are looking for ways to fight racial injustice. If you are in a position to donate to an organization fighting racial injustice, you are joining the ranks of individuals and companies who are supporting the movement with […]
The post Dear Analyst #33: Comparing one-time vs. monthly recurring donations to support racial justice organizations appeared first on .
The QUERY() function in Google Sheets gives you the ability to quickly filter and sort your data similar to how you might get data from a database. If you write SQL queries, the QUERY() function feels easy and natural to use. There are a few caveats as I discuss in this episode. If you want […]
The post Dear Analyst #32: How to use the QUERY function in Google Sheets on COVID-19 data appeared first on .
I worked on a “small” side project recently to sync data between Google Sheets and tables in Coda. The full blog post tutorial is here, and the GitHub repository is here. I started using Google Apps Script last year and it’s a super powerful way to connect different apps you use in the G Suite […]
The post Dear Analyst #31: Writing Google Apps Scripts to sync data from Coda to Google Sheets appeared first on .
Now that you’re staying home and picking up new hobbies and taking classes online, here are a few tips on how to learn Excel and spreadsheets from an online class. I have seen viewership on my own Excel classes spike since COVID-19 hit which has led me to think about the best way to learn […]
The post Dear Analyst #30: How to learn Excel while staying at home during COVID-19 appeared first on .
Have you ever wondered what an “array-entered formula” is? It’s an intermediate/advanced concept in Excel but in late 2018, Microsoft released dynamic array functions and formulas that “spill” into the cells below your current cell with a function. This makes writing formulas easier and less prone to human error, but there are some tradeoffs to […]
The post Dear Analyst #29: Working with dynamic array functions and formulas that spill appeared first on .
This spreadsheet tip is based on a question I get asked all the time when I teach (well taught) Excel at in-person classes: How do I fill a formula down to the last row of my data set without over-shooting the last row with keyboard shortcuts? This problem occurs with larger data sets where you […]
The post Dear Analyst #28: Filling a formula down to the last row of your data set appeared first on .
This is an Excel trick that’s not super complicated but super useful for labelling a simple table in Excel. Let’s say you have one set of labels along the rows (e.g. “Region”) and then another set of labels along the columns (e.g. “Month”). Cell A1 is now empty because you don’t know which label to […]
The post Dear Analyst #27: Splitting a cell diagonally to label y and x-axis and COVID-19 dashboard appeared first on .
Given the media attention placed on the coronavirus (COVID-19) in the media the last few weeks, I thought it was important to take a step back and look at the math behind infectious diseases and how diseases spread. I spend the entire episode taking a look at Going Critical, a blog post by Kevin Simler […]
The post Dear Analyst Episode 26: Data visualizations for infectious diseases/ideas during coronavirus (COVID-19) appeared first on .
This episode is based on a video and Tweet posted by Mr. Excel (Bill Jelen). Bill discusses an Excel challenge someone emailed him about regarding how to “transform” a badly structured table of data into a structure that makes it easy to do PivotTables and other downstream analysis. Interestingly, I received a ticket from a […]
The post Dear Analyst Episode 25: Structuring data challenge (denormalize data) with Get and Transform appeared first on .
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 […]
The post Dear Analyst Episode 24: Finding and ranking percentiles appeared first on .
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 […]
The post Revisiting Calculating Average Trends Across Time Periods in Spreadsheets appeared first on .
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/] […]
The post Dear Analyst Episode 22: Calculate win streaks for a pool of players in Google Sheets appeared first on .
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 […]
The post Dear Analyst Episode 21: Building No-Code Tools and Applications from Spreadsheets appeared first on .
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 […]
The post What it’s like teaching a week-long online data analytics course appeared first on .
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 […]
The post Dear Analyst Episode 19: Top 5 Signs You’re Using Too Much Excel appeared first on .