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 […]
Tag: coding
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 […]
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. […]
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 […]
A “Dicey” Experiment
You roll a pair of dice. My task is to predict which numbers will roll (total from both dice). How would I go about it? It’s obviously a probability problem. I want to find the probability of each roll for all possible outcomes. How would I arrange and solve it in Excel? Answer: Quite easily […]
Compare and understand: Spread and Consistency
Imagine you have a product line with an average price of $20, and another product line where the average is $100. Which has more spread? Imagine you have 3 players whose bowling scores you have (which are all over the place by the way) and you want to know which player is more consistent? Or, […]
Cataloging system audio files and playing them back
In this blog, I demonstrate how to enumerate all the system audio files that comes with Windows, put the list in an Excel file. Then use that list to play each one with a click of a button. First, let’s look at generating the list of audio files in our system folder. They’re in windows\media […]
There’s no formula for that! Grab a snippet of data-set.
Often I find myself working with a subset of the full dataset for experimentation, trials, and exemplification. For instance, my dataset may have 33,000 records but I want to work with about 100 records first in a separate sheet. Sure, I could (as most do) select the desired columns and continue to scroll down to […]
GOTO conference in Amsterdam: Agile
Although the message was delivered about the misinterpretations of Agile back in 2015, few heeded. Or perhaps few could hold back the ocean of this profitable industry with a broom while the proponents pushed a new framework as a methodology to the panicky companies entering the hashtag#programmanagement hashtag#agile 21st century. This clip is of Pragmatic […]