Monthly Archives: February 2014

A Tool to Find and Replace Words Within PowerPoint Charts

What !! you want me to change the name of a product in 120 charts because I missed to capitalize the first letter? And you want me to do this in the next 30 minutes? Do you have any idea that there is no inbuilt functionality in PowerPoint to do this?

This was the reaction my friend had when his manager asked him to change the product name in all the charts in the 150 slides PowerPoint deck that he just made. Since it was not the first time this had happened to him and he was pretty sure that it would happen many more times again, he reached out to me.

So, I made him a tool that allowed him to specify all the names that he wanted to change and their substitutes on an excel sheet and let the Excel tool take care of the rest. And for the greater good, I decided to share it with you guys. Download the tool here (Find Replace Within PowerPoint Charts tool; xlsm).

Using the tool is a simple two-step process and is explained in the image below.

FandRwithinPPTCharts

I hope your manager never asks you to do so. But if he does, you will find this tool useful :)

The Mathematics of Arrays

In the last post I explained the basic logic of array formulas and how they work. In this post I will be focusing on the mathematics of arrays – so what happens when you subtract one array from another or multiply or divide two arrays? How exactly does it work?

For the purpose of this post I will just be multiplying various arrays but you can basically do any mathematical (subtract, divide, add, etc.) or non-mathematical  (concatenation, find length, etc.) operation in the same fashion – literally anything and everything that you could think of.

Whenever I start with this concept, I get a common question – is this matrices multiplication that we learnt in school – so let me answer this, just in case – and the answer is NO. Array multiplication is a row wise multiplication. So each row of the first array gets multiplied with each row of the second array – in most of the cases.

Now, let’s look at all the possible cases of array multiplication:

Case 1: when both the arrays have the same number of rows and columns

In the image below, I multiply two arrays A & B (essentially range  C6:C10 & E6:E10). When I do this in a cell, I get the result that is displayed in the Output Array below. So, each cell in each row of the array A gets multiplied with the corresponding cell in array B.

Array2

So why did I say “each cell in each row”? To understand this let’s look at the image below: Continue reading