Monday, May 23, 2022

## Understanding buckets, bins, categorization (3/3)

In this multi-series of blog, I’ll touch on different ways to categorize data in buckets, or bins, and summarize in meaningful ways. Some will use Pivot Tables, some will not. But we’ll cover 3 common scenarios. Let’s do the third and final one here… The Scenario We have about 2,000 rows of data on sales […]

## Understanding buckets, bins, categorization (2/3)

In this multi-series of blog, I’ll touch on different ways to categorize data in buckets, or bins, and summarize in meaningful ways. Some will use Pivot Tables, some will not. But we’ll cover 3 common scenarios. Let’s do the second one here… The Scenario We have data on employees’ join-dates (TABLE 1). Objective: We want […]

## Understanding buckets, bins, categorization (1/3)

In this multi-series of blog, I’ll touch on different ways to categorize data in buckets, or bins, and summarize in meaningful ways. Some will use Pivot Tables, some will not. But we’ll cover 3 common scenarios. Let’s start with the first… The Scenario We have a 120K+ rows of data on wine bottle prices and […]

## Forecasting with Seasonality & Linear (Time-Series)

In one of my earlier blogs, I used linear forecasting method to predict a team’s scores in the future. While that was darn close to reality (when we checked with actual results), it did not mathematically account for fluctuations or seasonality and the outcome was always linear. In this blog, I show another method (that […]

## Relationships Explained by Statistics (humor)

The following charts are based on real datasets (plotted in Excel) that I created (for mainly humor, but probably not too distant from the truth) in order to attempt to visually depict relationship dynamics. The way to read them (or to get the most humor out of them), is to recognize that the x-axis shows […]

## Usages of OFFSET

OFFSET() is a nifty function that when used in conjunction with other formulas be  very useful at times. Here are some examples of how it can be used to optimize some tasks. In one of my earlier blogs, I demonstrated a formula to transform a matrix of data into a single, continuous column of data. […]

## Quick! Pick a number between 1 and 10!

“Pick a number between 1 and 10”—There’s been plethora of variations of such a game manifested in ways of magic tricks, simulations, statistics, and for sheer intellectual and nerdy curiosity. But is there an answer? Better yet, is there a pattern? If so, what is it…and how can it be explained? Some experiments claim to […]

## Simulating coin-toss in Excel

Most of us know that theoretically, there’s a 50-50 chance of getting a “head” from an unbiased coin-toss. There are numerous implementations and simulations done in virtually all programming languages around this age-old ‘riddle’. In this blog, I share a simple but very effective simulation in Excel. Here’s the simulation: As you can see, I’m […]

## Tip: Matrix to a column transpose

You have a table or a matrix composed of several columns and rows. Your objective is to transform it into a single continuous column using all the values from the matrix. So, you have this: And you want this:   How would you do it? There isn’t a simple formula that I could find (or […]