Sand Pendulums – Lissajous Patterns in Excel
Few days ago, I saw a beautiful homemade science experiment on Sand Pendulums on Bruce Yeany‘s YouTube channel. Go ahead and check it out. It is a cool project to do with your kids.
I will try this experiment with kids during school term holidays around Easter. But first, I wanted to try the simulation in Excel.
Simulating sand pendulum pattern in Excel
Take a look at the final simulation above. This is what we will create in Excel.
Reshaping your data easily – Case study [Pivot tables FTW]
Late. Jayaram, my uncle is also a teacher. When I was a kid, I used to spend a lot of time with him, learning all sorts of things. He taught me chess, maths and so many life lessons. I remember one such lesson very vividly. One day, he asked me to do something. I did it in a very long way. After seeing me struggle for several minutes, he chipped in and showed me how to do it easily. He then said, “when someone asks you where your nose is, you don’t twist arm around your head. You just point to your nose directly.”
The idea is that when you have a direct, simple way to do something, you should use it.
Nose and pivot tables… how are they connected?
We are coming to the point. Read on for full case study and solution.
Figuring out Employee Churn with Power Query [HR Analytics]
Let’s say you are the people manager at ACME Inc. You are looking staff list for the months – January and February 2017. You see that we had 4,000 employees in Jan and 4,200 employees in Feb. So what is the churn?
- Is it just 200?
- Or is it the sum of people who left and who joined?
- What if you want to find out how many people moved to new designations / departments or groups?
You see, churn is tricky to figure out.
So why not invite the pros? ie Power Query.
Find them and Extract them – VBA Macro
I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said ministry of MAGIC!!! ). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”
I explained him how to do this while chewing mouthfuls of rice & veggies.
But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.
So here we go.
Designing awesome financial metrics dashboard [tutorial]
In this amazing guest post, the winner of our 2016 dashboard contest – Chandeep – Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.
Thanks Chandeep.
Road Trip Planner Template [Excel Downloads]
We recently went on a road trip around parts of New Zealand’s north island. We have been to Taupo, Rotorua, East Cape and Napier. It took us 2 weeks, we drove more than 2,000 km and spent almost NZ $3,000 on the trip. Of course, being a data nerd, I made a plan of the trip in Excel and that helped us budget for this.
After getting back to home, I thought it would be fun to polish the planner workbook and share it with you all so you too can plan a fabulous road trip. So here we go.
Untrimmable Spaces – Excel Formula
Let’s talk about the untrimmable spaces.
We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.
So for example, if A1 has ” something and one more ”
TRIM(A1)
will give “something and one more”
We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31). Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.
The untrimmable spaces…?
The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”
Naturally I tried to TRIM().
But the data won’t budge. See above.
Hmm, let’s investigate why.