Today, let’s take a look at calculating percentages in Excel. We’re gonna take a look

at the formula you need to calculate percentage change and we’re gonna do it in a way that’s gonna help you remember it. If you’re working as a financial

analyst creating reports, this is a formula you need to get right. We’re also gonna take a look

at how we can do calculations that involve percentage

increase or decrease. So for example, we have a price. We want to increase price by 15%. What’s gonna be our end price? Let’s get to it. First off, let’s take a look at calculating percentage change. Assume your boss gives you an Excel file that contains actual sales

data and budget sales. Your task is to calculate

percentage change. Let’s take this to the board. So you have actual and you have budget. To calculate the difference, you’re gonna do actual minus

budget divided by budget. An alternate way of writing this formula is actual divided by budget

minus budget divided by budget, which turns into actual

divided by budget minus one. Now it’s all clear, right? So our formula is actual

divided by budget minus one. Double click on the bottom right side and send the formula down. (light percussive music) Now let’s move on to calculating percentage

increase or decrease. Our starting point is in column A. We want to increase the price by the percentage we see in column B. And if it’s minus, we want to decrease it. Let’s take this to the board. So we have our price. To calculate the price after we add the increase

or decrease to it, we’re gonna add this with price multiplied by that percentage. An alternate way of writing this is to factor out the price, so it’s price times one

plus the percentage. So that’s another way

of writing the formula. So in Excel, this would be starting price multiplied by one plus percentage change, close bracket, press enter. So if my starting price was 100, I increased price by 10%, I get 110. If this was 5% instead, I get 105. In Excel, there’s one thing

you have to be aware of and that’s how you input your percentages. One method to input percentages

is to add it while you type. So if I come here and type in 20 followed by the percentage

sign and press enter, the cell is automatically

formatted as a percentage. Another option is to

input it as a decimal, so 0.05 translates to 5%. So if I was gonna pull this formula down, we can see it adds up correctly as 5%. Now I can change this to

percentage and I get 5%. Now another thing you can do is to format your cell as percentage first before you input your full number. But don’t do it the other way around. So for example, here,

if I was gonna input 30 and then I decide, well,

this should be a percentage and I click to format

this as a percentage, I get the wrong number. So if this was already

formatted as a percentage, I go in and input 30, then it works fine. Okay, so let’s just

pull this one down too. So that’s it. Just remember, for percentage change, it’s new divided by old minus one or end divided by start minus one. For percentage increase,

we have the formula your value multiplied by one

plus the percentage change. And if it’s a percentage decrease, it’s your value times one

minus the percentage change. But if you input your

percentage as negative in Excel, you can use the same formula. Just drag it down. So the next time you’re asked to calculate the monthly or yearly change,

you know how to do it. If you like this video,

click that thumbs up. And if you want to

improve your Excel skills, consider subscribing to this challenge. (mellow music)

Hei helo. Can you teach and focus on vba more often? your teaching is fabolous

always love you. i bought your course on udemy and it's really useful for my work. thanks for always update new trick.

Nice video I need it, first time I see you while teaching on board….

thank you very much

Leila Gharani I watched your video they are very useful and help me in my duty.

If it's possible for you please teach us ( DGHET) formula.

thanks so much

This was good. Thanks. Could you do a quick video on the best way to calculate compound annual growth rate using Excel?

Very nice video, you are very good to simplify every problem .

Keep posting such informative videos. Thanks for sharing !

Thank you so much for very great video. It helps me lots. I have a question, related with IF function.

my question is :

Richard asks you to calculate the membership prices for the first quarter memberships sold in the gallery ticket office. The individual membership price is $50. Students pay a discounted price of $25.

In cell F5, create a formula using the IF function to determine the membership price based on the following criteria:

a. If cell D5 (the Category) is Student the price is 25. (Hint: Student must appear in quotes. Do not enter $ for the price.)

b. All other individual members must pay 50.

Copy the formula in cell F5 into the range F6:F13.

So much informative and proper video. Keep updating these like video. I'm also getting inspired by these video, thus i've also created a youtube channel on Microsoft Excel.. Thank you..!!

Like…Thanks Leila.

Really useful guide about percentages. Thanks!

Lovely .. straight & clear.!!!

Excellent, as usual. You explain things clearly and concisely.

For me, this one wasn't as useful as the others. I'm a certified high school teacher, so I know the math, and I learned spreadsheets on Supercalc 5.1 ( ever heard of that??), so the cell formulas were pretty straightforward.

However, this brings us back to my first point: excellent, as usual. Since many people doing this work may not know the math, it's great that you cover it, just to be sure. I think people would rather learn math from you, as they need it, than suffer in my math class. LOL.

This is really a great service you are doing. Thank you, and God bless you!

Love You Mam

Awesome Leila and thanks for sharing your knowledge. Would like to learn more from your videos & sessions. Can you please ping your mail address to [email protected]

Let me Share few tips.

Tip1: Conventional way of calculating Gross Margin is (Sales-Cost) /Sales. But you can use (1-Cost/Sales) for ex (152-68)/152 = 55.3% or (1-68/152)= 55.3%. both will give you the same result.

Tip2: You have a Cost Price lets say 20 and you want to fix the sales price which can give you 30% Gross Margin. How do we get that sales price which can give me 30%GM? Its simple.

Cost/(1-GM%) in other words (20/1-0.3)= 28.57 is the sales price that we want to sell in order to get 30%GM

Very good it helped me so much… ๐๐๐๐๐๐๐๐๐๐ป๐ป๐ป

why would it be 'more' right than the first method? is the 2nd method more efficient? thanks.

Great tut

Absolutely Amazing. Love it

This is the only "useful" and non-time wasting channel I follow on YouTube! Really happy I found it!!

Version 2010 and on (not sure about earlier) of the pivot table does this also. Even if you prefer the worksheet with formulas, it's a great way to check.

I used to do the longer formula of %=(B-A)/A. Figuring out %=B/A-1 was a help. But also fun to see it as a reduction of B=A*(1+%)

omg yep this is what i confuse the most even to this day. nice refresher

very cool, elegant formula….

thank you, is there a way to show that a name with a score has moved up or down a ranking list?

Hi Leila,

Thanks for sharing nice basic concept. And hope not only excel users but other people will also be clear about these percentage confusion.

Anyway, as an Excel guru can you please share any video on "Unique Random No Generation by formula."

I had searched a lot and found some solution but they are not satisfactory. By Match and Large with Rand List is somehow suitable solution but I am looking for a formula which will replace RANDBETWEEN like RANDBETWEEN.UNIQ.

Hello Leila Gharani Madam,

Can you do a video on, how to make unique values of Rang("A:C") in vba ? Using Advanced Dynamic Array function we can do. Still "Unique" function is not available yet.

If you do this, more people will get knowledge on this.

Thanks for videos.

Hi thanks for your support

I have a question

Can we record calls live by macros

Kindly suggest

Hello Leila. Iยดve just bought your course bundle today, but I am having some issues to log in to my account, teachable doesnt recognize my email in their data base. Do you have any support email to get this problem sorted??

Thanks you so much for your videos. You are a very good teacher in excel. I have learned a lot through your videos. Even if I'm french, I can understand easely what you said, because you have a very good skills in teaching. So, I'm waiting for new videos, especially about datavisualisation and making beautiful graphs. I hope you can understand me, I'm sorry for my english.

your awesome! i learned 2 things in one day and applied those in my work….

THANK YOU for this video. This is the help I needed to simplify my formulas.

It's a very nice trick to get result of percentage changes.

Thanks to sharing this video.

Hi Ms. Leila,

Please help me with correct formula of Growth

if base sales is negative in value and in current sales is positive in value

Do you have any videos dealing with projections, such as if a company is trying to figure out how many people to hire in order to keep staffing at where they need it to be so they can hit production goals? how they could use historical attrition data, create an attrition rate for each months of the year that would help them to predict a future staffing number needed to stay at plans by year end? For example, a Department knows they need to keep 100 people all the time to hit production goals, but they would like to know if there are certain times of the year they probably have more people leaving the company and they want to plan on hiring more then too, so that by the end of the year and all year through they remain close to the goal of 100 people. What would be the best way to present that data?

I like the way you use the whiteboard to explain algebra. What's your degree?

Great

Mam why we are putting 1+%

Awesome!! ๐๐

Thank you

For finance folks all this stuff is basics 101. But one thing that is tricky is when percentage change involves a negative number. I use the ABS function but there seems no standard way to handle that.

How Can I Put Percentage On Sheet?

A-B always for the numerator.

But ABS(B) for the denominator.

Your formula doesnโt work when B is negative.

Thank you! This video helped me pass an exam!

Question: in your first example you calculated actual v budget percentages for individual entries in the row. If I wanted to calculate the overall average budget v actual between all of the entries, what would be the best way? Should I run the average of the % in the column? Or should I add all my numbers and denominators and then divide?

Been wondering how this was calculated for a while. Thank you!

very nice. Thanks for sharing such a grate lesson.mam

You are the best

Thank you. Very nicely done

Thanks very helpful information

Thank you math teacher

Wow

you are really a great teacher I have never seen like you before.

I have some confusing thing is that, I want to now how can I post all the transaction from general journal to ledger "by used one or more of Excel Functions" ?

Thank you so much for ๐

I'm waiting for your reply.

Hello.

Great video as always.

Can I ask for help with profit on return rather than a straight percentage ?

=A1/(100-B1)*100

With A1 = 100, ย B1 = 10,ย Profit on return formula above gives a result of 111.1111.

This gives you full profit of B1 from the result rather than on cost. If you take 10 % from 100.

Is there a better way, a Leila Gharani way to achieve this ?

Thanks.(This is without cells set as %)

I have learned so many idea from your videos.please keep teaching us.

Love you Leila. Many thanks for your video tutorial. I have improved my skill in ms excel.

Having seen a lot of your videos I have to congratulate you on your detailed approach to any issue that you are trying to explain. I consider myself an advanced excel user and still there are bits that I am learning from you. I see a lot of %changes as my daily job and I see people struggling. First you have to identify your starting point and then identify your terminal point. For example Budget sales is my starting point and the Actual sales my finishing point (it can easily be the other way around, depending on what you want to present). With the %chnage we are trying to find out what is the number to add on top of the budget sales (starting point) so to arrive to the actual sales (finishing point). And always the %change = (Terminal point / Starting point -1), that is the only that you have to remember so in your example is Actual/Budget-1. You might have year1 and year2 data the %change is different if the starting year is year1 and different if is year2, but always the correct formula is (Terminal point / Starting point -1). Special cases If year1 sales=120 and year2=0, then the %(year2 vs year1) = (-100%) but if year1=0 and year2=120 the %change is not defined as there is no number to multiply 0 (the starting point) and arrive to 120 (finishing point). As an analyst when I came across these cases I rather have a text saying something like "No sales in previous year" but not +100% that is totally incorrect.

You rock! I became your fan. Your videos are spectacular.

Beautiful and smart woman…

Una vez mรกs; excellent work. Thank you, gracias

thanks a lot

Very useful video beautiful lady

It's really amazing video and so helpful tips Thanks for sharing

Graduated from statistics major and the first thing I had to learn when hired was how to calculate % growth in excel.

u r so smart

Good, very useful knowledge.

Super useful, Thanks for sharing

Love it …thank you Leila

This is fourth grade math, maybe third.

Videos are amazing. Watched you do this in IF functions and was slightly confused. Thanks for clearing up

Basic indeed, but always nice to remember…

This is maths not excel

I am new here. Really nicely delivered your knowledge! Love from Pakistan ๐

You are great. Your video really helped me.

Another way of calculating actual value is: (You = VeryCool)

100% cool video

Your videos are nice to watch, hear and are helpful also. Damn thats fair enough to subscribe

I love you mam

Excellent, Leila! A lot of people – especially in the media – misuse percentages. For example "the amount of sales decreased by 200%" – impossible! Or "they increased by 300%" when a threefold increase was what happened, that should have read "they increased by 200%" (or better "to 300% of the initial figure").

Can I ask what is the signficance of the either -1 at the begining of the formula or the end of the formula

Thank you for all that amazing info. I have made a function with many "IFS" and I was wondering if there would be a shorter function that would give me the same result … I have to pay different percentages of taxes on different amounts; <=5,000 x 6% & <=15,000 x 11% & >15,000 > 16% ….. this function works but very long ….. would you have a better one =IF($N$15<=5000,$N$15*6%,IF($N$15<=15000,$N$15*11%,IF($N$15>15000,$N$15*16%)))

This particular video is one of a kind, but guess what? you are such a genius!

I was distracted the whole time by that angelic face

Thank you for all the videos I learned a lot from you, I have one question

How do you find the percentage increase in a negative number?

for example:

previous year: -3,162

current year: 13

your method will show: -100%

do you recommend to use ABS function to solve the wrong result?

BTW I have more than 100,000 data line in Excel, so I prefer to use one method that works for both positive and negative numbers

Is percentage change the same as percentage variation ?

Thanks for the video! easy!

Leila, for short timeframes, you can also calculate % change as ln(new/old).

That way, if you have a sequence of events, such as daily stock market data, you can just sum the % change of a range (ie week) to get the % change in that range (ie for that week)

No deberรญas explicar temas en los que no estรกs muy segura. Se nota tu inseguridad al exponer un poco de รกlgebra.

Good stuff. How about irregular percentage increase in n years? Is there Leila version channel for Word?

again and again i wont stop thanking you ๐ love your videos

Fantastic.

Great video. Someone might have already mentioned this but I was always taught to mark up using P/(1-%) and down using P*(1-%) that way if you mark and and then back down you end up the same number. Using the method you describe you will not get the starting value.

Hi

Leila, thanks for the video as everyone is saying your videos are great. But i have a question now it might be my math / or some error on my part that is the problem but why can you not reverse this. So assuming that A2 is actual and budget is B2.

=A2*(1+10%)

would give us actual +10% (so if actual is 100, and budget is 10% more then budget would be 110

) now if i wanted to check that increase could use

=A2/B2-1

But that show 9.09% not the 10% i asked to be added to. i would have expected to see 10% here the same as i specified.

Amazing ๐

Thanks!!! Love your work.

I am so surprised! I was thinking about how to calculate the change in variance analysis – believe me once I opened youtube it showed the useful video – was that by chance? Leila covered every valuable tips ๐

Thank you so very much

Your videos have been really really helpful

Thanks

how to calculate for this case.

actual 2019 = -2

target 2020 = 3

growth = ? (formula)

Tnx to share

Thank you, Leila! Be wary of margin and markup – e.g. cost= $80 and sale price = $100; the markup is 20/80 = 25% but the margin is 20/80 = 20%.