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 […]
Category: Analytics
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 […]
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, […]
Three sure ways to find Outliers in your data
I often see questions about how to find outliers in a dataset of any size the effective way. From what I’ve seen, there are many suggestions/responses and most of them are INCORRECT unfortunately. Here, I’ll show you 3 accurate and proven ways to find outliers in any data. Let’s start with a diverse dataset: I’ll […]
Creating Word Cloud accurately and easily in Excel
There are plenty of designers, templates, and online services that let you create Word Clouds (aka Tag Cloud), but do you know how to create one yourself in a short notice, without paying anyone anything? If you have Excel and some creativity, then you can create a Word Cloud just as good as any! It’s […]