Working with Percentages in Excel

Introduction

If Excel is part of your regular toolset, it is likely that some of your formulas use percentages. Let’s face it, it is pretty impossible to completely avoid percentages in Excel.

Why this Post?

When I started to teach Excel in the mid 80’s, I quickly realized that if I wanted to become an efficient Excel instructor, I had to be totally fluent with percentages! I decided to tackle all sorts of problems involving percentages and quickly realize that, in the end, there are not that many rules to remember. It was much easier than I initially thought!

Why this post (Part 2)

The difficulty for many (and I hear that statement regularly) is that they repeat to themselves : I hate percentages! Of course, they don’t actually hate percentages! Most likely, it was not explained in a simple (and practical) way as well as shown real life examples. Let’s embark on a trip with the goal of making peace with percentages!

Who invented Percentages?

No one person invented percentages. The concept developed throughout history. In Ancient Rome mathematical computation were expressed in fractions of 100. This concept later evolved into percentages. The word percent comes from the Latin phrase per centum, and it means “for each hundred.”

What is a percentage?

Amazingly, a percentage is simply a fraction! Now everyone knows what a fraction is. Let’s prove it! Would you agree that if someone wants to give you 50% of a cake, you will receive half of that cake! So, if 50% = 1/2 then 25% = 1/4 and 75% = 3/4. See! A percentage is indeed a fraction.

How does that help me to understand percentages?

The explanation above states that percentages are always expressed in fraction of 100! Now, that’s a solid hint! In French (like Latin) the word Percent translates “Par Cent” which becomes “Per Hundred” in English. This means that, every number expressed as a percentage, is really a fraction of 100.

Since a percentage is a fraction (we discussed this earlier) then 50% = 50/100 and 67% = 67/100. Finally, 100% = 100/100 which equals 1. Indeed, 100% of a pie is one complete pie.

How do I calculate a percentage?

Suppose that at gathering, 38 people out of 65 drank orange juice. You want to know what percentage drank orange juice. Look how amazingly easy it is to get the answer to that question:

Since we said that a percentage is a fraction, let’s divide 38 / 65. In Excel, I entered 38 in cell A1 then 65 in cell A2 then used the formula =A1/A2 and got the result 0.58 (I rounded for simplicity). I then use the Percent Style button to format the result and, Excel displayed 58%. That is the answer you were after!

Calculating a Percentage

It’s Simple!

To calculate the percentage of a number over the total amount, divide the number by the total amount.

So, any possible question in the Universe that uses the following pattern: There was “A” things out of a total of “B” things that did whatever. Then you can calculate the percentage of A over B by … dividing A over B!

Another example please!

Okay! In a village, 275 people got the flu out of a total population of 534. What is the percentage of villagers that got the flu? The rule never changes! In Excel (or using a cheap calculator) I divide 275 (number of people sick) by 534 (the total population) and I get 0.514. So 51.4% of the villagers got sick. By using 1 or 2 decimals, you make your answer slightly more precise for the reader, especially useful if you are dealing with large amounts.

How do I calculate the percentage of a number?

Now this is a different question! We already have the percentage (example 50%) and we also have the total (example 500).

If, to calculate the percentage between 2 numbers you need to divide them, then to do the opposite you need to multiply them. Example: You are told that 25% (which is in reality 0.25) of a group of 340 guests want to eat beef. How many meals with beef will you prepare? Now, we don’t want to calculate a percentage, right? We already have that percentage!

What is the percentage of a amount

To calculate (or extract) the percentage of an amount, multiply the amount by the percentage.

So, to calculate 25% of 340 you can use Excel to multiply 25% by 340 (0.25 * 340) and the answer is 85 (which is one quarter of 340).

Another example please!

Sure! I have heaps of time. In a town of 24,500 inhabitants, 38% earn more than $80,000 per year. How many inhabitants does this correspond? All we need to do is to type 24,500 in a cell and 38% in another cell and multiply both cells to have the result. If you try this you will find that 9,310 inhabitants fall in the category described above.

One last example please!

Let’s ask a simple question but in a tricky way. If you stuck with me from the beginning you will certainly pick up the little twist in this one, Okay? Situation: In a group, there was 34 people speaking French and 59 speaking Italian. Question: What is the percentage of people speaking French? I’ll let you think about this one a bit. Hint! The rule is the same as all other examples we discussed above. Answer further down.

Let’s Move On…

It is now time to establish an important point before we move on to more interesting things about percentages. The following may sound a bit simple for some, but for the benefit of all who struggle with percentages, we will spend a few moments discussing a special case of percentage.

What do you think would be the percentage if every element of a group fall into a category? For example, suppose that at a function, every man wore a tie. What would be the percentage of men wearing a tie? I can hear all of you thinking: Of course it would be 100%! And you’d be perfectly correct!

So the question arises. If the number for 50% = 1/2 and 75% = 3/4 then what is the number for 100%? From the peanut gallery I can hear a faint voice whispering … One? Good on you I shout!

The percentage 100% is equal to 1

So 100% of a team is the whole team, or 1 team. Again, 100% of a cake is one cake (the whole cake).

Indeed, 100% = 1. And this is very important to remember when we want to increase or decrease a value by a certain percentage.

A situation where this is used, is when you want to express to the manager that there was an increase in the production of widgets of 20% in the last quarter.

Increasing a number by a percentage

Suppose your manager says that this year’s production increased by 20% from last year. How do you know how much is that? First, you’d need to know what was the production last year (which would be an unchangeable number since it’s in the past).

Here we do not want to calculate 20% of the production, we want to know what is last year’s production increased by 20%. Since last year’s production is 100% of it and it increased by 20% then we need to calculate 120% of the previous year’s production!

However, you will probably never hear someone say: This year’s production is 120% of last year. So to find how much we produced this year you need to multiply last year’s production by 100%+20% or more simply by 1.2 (recall that 100% = 1).

So what is the formula that increases a value by a percentage?

Suppose that last year’s production was 4,500 units. This year’s production represents an increase of 20% over last year then the formula would be: 45000 * (1 + 20%). The brackets (1 + 20%) would equate to multiply the 45000 by 1.2. The final answer is 54000.

Increasing by a percentage

To increase an amount by a percentage, use the formula: Amount * (1+Percentage).

For the record, remember that the brackets are always evaluated before the multiplication? That’s why we can say that the formula internally becomes 45,000 * 120%

Just to review briefly the method used above:

• The formula: 25,000 * 100% would return the same amount since 100% = 1.
• The formula: 25,000 * 90% would reduce the amount by 10%.
• The formula: 25,000 * 120% would increase the amount by 20%.

Another example please

Suppose your manager tells you that your team needs to increase their sales by 8% in the next quarter. How do you figure out what that means in term of sales?

Based on the previous examples, you know that you must at least know what were your sales in the previous quarter. Suppose that is was $22,500. You open Excel and type that figure in a cell (say in A1). In the cell below (A2) you type the target percentage of increase: 8%. You want the target sales in cell A3.

Cell A1 contains 22500 and cell A2 contains 8%. The formula in cell A3 would be: =A1*(1+A2). The result is 24,300.

Decreasing a number by a percentage

What I really like with the approach discussed above is that the difference between the formula that increases a value by a percentage and the one that decreases by a percentage is 99% the same! To decrease a number by a certain percentage just change the plus sign with a minus sign!

The manager of a department is told that his budget will drop by 5% in the next financial year. Knowing that his budget for the current year was $40,000 what will be the amount for the next financial year?

Based on what we discussed above, the formula is: [Current Year Budget] * (1 – 5%).
This section continues further below in the section: How does that work again?

Answer to the question regarding the French and Italians. If you answered 36.6%, then you’re on you way to become an expert to be sure!

How did you get to this answer?: Okay. What was the question again? What was the percentage of people speaking French knowing that 34 are French and 59 speak Italian? The rule is to divide one of the number by the total. The resulting fraction becomes the percentage. So since there was 34 people speaking French and 59 speaking Italian the total of the group is 93. We want the proportion (or slice) of French persons (34) over the total so 34 / 93 = 0.3655. Formatted as Percentage with one decimal and the answer becomes 36.6%.

How does that work again?

Well, if (1 + 5%) equals 105% (an increase) then (1 – 5%) would equal to 95%. And everybody will agree that if I give you 95% of what you had before then you suffer a 5% loss!

Decreasing by a percentage

To decrease an amount by a percentage, use the formula: Amount * (1-Percentage).

In Summary

Percentages are not hard at all and they’re here to stay, forever! So let’s get on with them! 😎

• To calculate the percentage of an amount: Multiply the amount by the percentage.
  • Example: 387 * 8% — 8% of 387
• To increase an amount by a percentage: Multiply the amount by 1 plus (Increase) the percentage in brackets.
  • Example: 1200 * (1 + 8%) — Increase 1200 by 8%
• To decrease an amount by a percentage: Multiply the amount by 1 minus (Decrease) the percentage in brackets.
  • Example: 1200 * (1 – 8%) — Decrease 1200 by 8%

Now, it will surely happen that someone will tell you that they use another approach and that they feel is simpler. Just acknowledge with a smile but, do yourself a favour, stick with what you learnt in this post. You will never regret it!

Daniel from ComboProjects