My wife teaches these percent applications to junior highers. They do it by hand instead of in Excel. She said your approach to writing the formulas gave her new ideas on how to explain it to them. Thank you!
*Wasted whole night using a calculator & manually entering % addition data*- Now I have learned the time-saving way. Much appreciated & thanks for your efforts
how person dislike this video and its content. Leila did huge effort to simplify, make easy to understand and hoe to implement the subject. moreover, she explained a nuances too. thank you Leila for your generous knowledge sharing and all you've done.
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)
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").
Yes, and years ago I explained to someone that 100% more then is the same as 200% as much of. I might have a problem too where when media makes this mistake I interpret it as what they said actually means and not what maybe they might actually mean. It's not my fault I'd they are saying it wrong.
I know enough about xcel to be dangerous. seriously thinking about taking one of your courses but not sure what I need. I'm a small business owner (semi-retired) applying for PPP loan and needed to get percentages of loss from 2019 - 2020. your formula was just what I needed. need to wait for PPP grants or loans before spending any money but am now subscribed. thank you. currently using xcel 2010 but I think I have a newer version. just not installed
These formulas are really helpful. Thanks for this video. However I have a tip for anyone who wants to use these formulas. When I work with clients or fresh recruits or sometimes even my boss 😀, I find it easy to explain it to them by using the formula in layman terms. For example %change would be (Actual-Budget)/Budget.
I couldn't concentrate admiring your beauty. Thank goodness I found you. I have to do a price increase to my inventory (1000+ products). I'm going to buy the course. You just made me the day *night. Thank you!
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.
Dayum! You Rock. I was playing with this not long ago and had to give up because I was making all the mistakes you mentioned. Thank you for all your help.
Hi Leila, I have an amendment for your formula. In Excel if you want to find out what percentage represent a value from a different one the right formula I've found (I'm using EXCEL 2007) is: =((new value cell/initial value cell -1)+1). In this way, if I am formatting the cell as "%" the result showed will be exactly the value of the percentage I am looking for. Thank you for your work!
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!
I have been being in the generic finance industry in Greece for about 12years. Recently I noticed your videos and your channel and I think of them as the best I have ever seen in this subject, though I am an excel savy, and excel is my daily working tool for a decade and the subject of my training duties to new staff. However I didn't get the purpose of this video.
I am sorry, I watched the video again. At least in Greece, if your boss found out that you watched this video in order to managed to report % changes you are fired. Except for the cases you are someone of his relatives or a relative of a polititian.
Was taught an easy way to remember percentages framed by a question “what percentage is B of A” = formula will be “is over of x 100” Eg B divided by A multiplied by 100 - excel formula =(B1/A1)x100
Leila, you made me look back in past in my math's class. The only difference; my teacher was not adorable as you are :) :). Thanks for upload. By the way let me share formula of Future Value based on the % trick that you showed. E.g. how much $ 10000/- become after 6 years giving interest rate of 8.16% compounded annually. No need to use any financial function of Excel. The simple way is =10000*(1+8.16%)^6. If its semi-annually compounding then divide the interest rate by 2; multiply the power by 2= 10000*(1+(8.16%/2))^12 and same way for quarterly output..
@@olusegunaina9097 Thanks Aina; one more formula of EMI; little lengthy, but once you get hang of this no need to use PMT function of Excel: 1. Divide the interest rate by 12 2. Multiply the principle amount by the result in point (1) 3. [1+result in point (1)]^ no of months 4. Result in point (3) -1 5. Finally, Result in point (2) multiply by Result in point (3) and divided by Result in point (4) IS THE EMI (equated monthly installment). You can surely try this; not even a single penny difference will come if you cross check with PMT function; Certain things are really fun when done in CLASSIC WAY :)
This “devide the percentage by the number of periods” argument always gets me. Let’s focus on a particular example: yearly vs. monthly. If your yearly% = 12%, how much is the equivalent monthly% ? And then: why? What reasoning do you follow?
An auditor, finance manager or accountant tip: When calculating Budget vs Actual (% change) the stakeholders (users of financial information) might want to know how much of the budget was realized. In that sence, you would have Actual / Budget. For instance, the first row 14,432/15,113 = 95,49% would give the information that we only managed to achieve 95% of the sales budget, indicating a deficit. The way you calculated is more appropriate when you want to see %change over different years, actual being the current year and budget being the previous.
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 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 :)
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.
I'm not a mathematician but I disagree. Let's say we increase from 100 to 120. That's an increase of 20% on a base of 100. My calculation would be 120/100-1 = 20%. However, by using your formula for marking up I get to 125: 100/(1-20%)=125. The other way, if we go from 120 to 100 that's a reduction of -16,67% from a base of 120. Or, did I misunderstand your calculation?
@@LeilaGharani What I am talking about is this: If I buy product A at $100 and I want to make a 20% margin on it, then using P/(1-%) will give me $125. 80% of 125 of 100 the original cost. 25 is 20% of 125 the margin I wanted. This is how all sales software I have seen works. See this for a much more elegant descripton mathcentral.uregina.ca/QQ/database/QQ.09.06/h/drew1.html Please don't get me wrong though, I love your videos
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.
Hi Leila, could you please use your charming style of teaching and English, and explain audience the following: when the VAT increases from 20% to 21%, the increase is not by 1% but by 5%. Many people do not understand this, even editors in TV, newspapers etc. Thank you 😉.
To be honest, if you're a financial analyst, or any kind of analyst for that matter, and don't master these basic concepts then you REALLY should not be doing your job. On a side note, as always, great video again, Leila!
3:26 Yep. That is because Excel is expecting a decimal - and it converts the decimal (for example, your .05 example) into a percentage (%). Therefore, 30 is expected to be input as .30 which becomes 30% (multiplying it by 100). On the other hand, it also multiplies 30 by 100 and that’s why it returns “3000%” (3,000%). On the contrary, if you input the decimal beforehand - in comparison to formatting as a percentage (.05, for example) it is already the correct number and just needs to change the formatting.
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.
Grab the file I used in the video from here 👉 pages.xelplus.com/percentage-change-file
My wife teaches these percent applications to junior highers. They do it by hand instead of in Excel. She said your approach to writing the formulas gave her new ideas on how to explain it to them. Thank you!
I would rather do it by hand
I learn something EVERY time I watch your videos. Thank you!!!
EXACTLY what I needed. I'm working with budget information, and this was perfect. And right at the beginning of the video. Thank you!!
My pleasure Chuck.
Thanks I been calculating percentage for 20 years in excel, Never thought of this smart way to do it.
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/100 = 20%.
margin is 20/100 = 20%
@@elbaymammadyarov8120 Correct!
*Wasted whole night using a calculator & manually entering % addition data*- Now I have learned the time-saving way. Much appreciated & thanks for your efforts
Glad it helped!
I use DAX all the time and love learning from your tutorials and not only that you are a beautiful intelligent lady!
You explained that very well and understandably, Ms. Leila Gharani. I always enjoy watching your videos.
how person dislike this video and its content. Leila did huge effort to simplify, make easy to understand and hoe to implement the subject. moreover, she explained a nuances too. thank you Leila for your generous knowledge sharing and all you've done.
I appreciate that!
Leila, you are my saver, You help me every time. THX. BR from Slovenia.
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)
This was good. Thanks. Could you do a quick video on the best way to calculate compound annual growth rate using Excel?
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").
Yes, and years ago I explained to someone that 100% more then is the same as 200% as much of.
I might have a problem too where when media makes this mistake I interpret it as what they said actually means and not what maybe they might actually mean. It's not my fault I'd they are saying it wrong.
I watched your video this morning and used it already in the afternoon. Thanks, keep them coming.
Awesome! Thank you!
I know enough about xcel to be dangerous. seriously thinking about taking one of your courses but not sure what I need. I'm a small business owner (semi-retired) applying for PPP loan and needed to get percentages of loss from 2019 - 2020. your formula was just what I needed. need to wait for PPP grants or loans before spending any money but am now subscribed. thank you. currently using xcel 2010 but I think I have a newer version. just not installed
Welcome aboard!
We all love you, you are a big source of knowledge for the entire world, thanks for what you do.
These formulas are really helpful. Thanks for this video.
However I have a tip for anyone who wants to use these formulas. When I work with clients or fresh recruits or sometimes even my boss 😀, I find it easy to explain it to them by using the formula in layman terms. For example %change would be (Actual-Budget)/Budget.
Anish Pillai agree.
Much easier to understand and explain. Basically the difference divided by the starting figure.
I couldn't concentrate admiring your beauty. Thank goodness I found you. I have to do a price increase to my inventory (1000+ products). I'm going to buy the course. You just made me the day *night. Thank you!
That's great. I hope you'll enjoy the course.
Thank you Leila for your amazing tutorials, this one is so basic yet highly needed one. Absolutely loved this.
There is one vlogger . Its channel is innorative. He discussed computing growth. This his formula =(current-previous)/current
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.
Very good explanation. Thanks for the valuable feedback Christos!
Thank you so much for the video on percentage increase and decrease.
You're most welcome.
thank you lol, I needed this for my macroeconomics class
This is my go to lady for Excel questions! Thank you!
Thanks for the video. Simple and easy to understand.
You're welcome, Deon!
I finally want to learn how to work with excel and this will be a great learning base.
Dayum! You Rock. I was playing with this not long ago and had to give up because I was making all the mistakes you mentioned. Thank you for all your help.
You're very welcome. I'm glad you found this useful.
Hi Leila, I have an amendment for your formula. In Excel if you want to find out what percentage represent a value from a different one the right formula I've found (I'm using EXCEL 2007) is: =((new value cell/initial value cell -1)+1). In this way, if I am formatting the cell as "%" the result showed will be exactly the value of the percentage I am looking for.
Thank you for your work!
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!
Thanks for the kind words Mark. I'm glad you like the videos. I'm sure no one is suffering in your class :)
Sister your videos are very knowledgeable. Love and respect from India
Thanks Leila Gharani for your smart video !!!
I have been being in the generic finance industry in Greece for about 12years.
Recently I noticed your videos and your channel and I think of them as the best I have ever seen in this subject, though I am an excel savy, and excel is my daily working tool for a decade and the subject of my training duties to new staff.
However I didn't get the purpose of this video.
I am sorry, I watched the video again. At least in Greece, if your boss found out that you watched this video in order to managed to report % changes you are fired. Except for the cases you are someone of his relatives or a relative of a polititian.
@@charalampos4016 Your comment made me laugh :)
Was taught an easy way to remember percentages framed by a question “what percentage is B of A” = formula will be “is over of x 100” Eg B divided by A multiplied by 100 - excel formula =(B1/A1)x100
Was Always looking for this concept.. Finally found it. Thanks for teaching elemental basics and then excel!
Leila, you made me look back in past in my math's class. The only difference; my teacher was not adorable as you are :) :).
Thanks for upload. By the way let me share formula of Future Value based on the % trick that you showed. E.g. how much $ 10000/- become after 6 years giving interest rate of 8.16% compounded annually. No need to use any financial function of Excel. The simple way is =10000*(1+8.16%)^6. If its semi-annually compounding then divide the interest rate by 2; multiply the power by 2= 10000*(1+(8.16%/2))^12 and same way for quarterly output..
That's great! Thank you for sharing Sachin.
Thanks Sachin. This came in handy
@@olusegunaina9097 Thanks Aina; one more formula of EMI; little lengthy, but once you get hang of this no need to use PMT function of Excel:
1. Divide the interest rate by 12
2. Multiply the principle amount by the result in point (1)
3. [1+result in point (1)]^ no of months
4. Result in point (3) -1
5. Finally, Result in point (2) multiply by Result in point (3) and divided by Result in point (4) IS THE EMI (equated monthly installment). You can surely try this; not even a single penny difference will come if you cross check with PMT function; Certain things are really fun when done in CLASSIC WAY :)
@@sachinrv1 wonderful Sachin. Thanks
This “devide the percentage by the number of periods” argument always gets me.
Let’s focus on a particular example: yearly vs. monthly.
If your yearly% = 12%, how much is the equivalent monthly% ?
And then: why? What reasoning do you follow?
I always watch your videos with great interest.
Thank you very much!!
Brilliant. Exactly what I was trying to do. Thank you!!!!!!!
An auditor, finance manager or accountant tip:
When calculating Budget vs Actual (% change) the stakeholders (users of financial information) might want to know how much of the budget was realized. In that sence, you would have Actual / Budget. For instance, the first row 14,432/15,113 = 95,49% would give the information that we only managed to achieve 95% of the sales budget, indicating a deficit.
The way you calculated is more appropriate when you want to see %change over different years, actual being the current year and budget being the previous.
Thanks for the feedback!
@@LeilaGharani thank you for the videos, I'm watching everyday and learning so much
Thanks Leila! I teach statistics and very happy to see your lecture!
Thank you I liked that . I can use it for my students.
Great!
Wow..This is very good ...i learnt a good lesson from it. Thanks.
Glad it was helpful!
I have learned so many idea from your videos.please keep teaching us.
I'll definitely do that. Glad to hear the tutorials are helpful.
explained that very well and understandably
Glad it was helpful!
Thank you Leila. Other ways to do basic math formulas would be nice. You could make it a special series of videos.
simple enough and that's ok. Sometimes a review of the simple things is important!! Thanks, Leila!
Thank you! So simply laid out, appreciate it.
You're very welcome!
Cool. Makes learning so meaningful. Thanks for sharing the tips.
A very good video!! Congrats; greetings from Brazil
Thank you Luis! Cheers!
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.
always love you. i bought your course on udemy and it's really useful for my work. thanks for always update new trick.
Thank You Very Much Leila Gharani I like your video very much
This particular video is one of a kind, but guess what? you are such a genius!
This one was a huge efficiency for me!
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 for your efforts
Ree-port 😂…thank you for all the great videos. 🙏👍
Cool % video, Teammate!
@excelisfun you and Leila are the best Excel teachers.
Thank you Teammate Mike :)
I like you both and all of your training
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.
I'm not a mathematician but I disagree. Let's say we increase from 100 to 120. That's an increase of 20% on a base of 100. My calculation would be 120/100-1 = 20%. However, by using your formula for marking up I get to 125: 100/(1-20%)=125. The other way, if we go from 120 to 100 that's a reduction of -16,67% from a base of 120.
Or, did I misunderstand your calculation?
@@LeilaGharani What I am talking about is this: If I buy product A at $100 and I want to make a 20% margin on it, then using P/(1-%) will give me $125. 80% of 125 of 100 the original cost. 25 is 20% of 125 the margin I wanted. This is how all sales software I have seen works. See this for a much more elegant descripton mathcentral.uregina.ca/QQ/database/QQ.09.06/h/drew1.html
Please don't get me wrong though, I love your videos
Very nice video, you are very good to simplify every problem .
Keep posting such informative videos. Thanks for sharing !
Glad you find the video helpful!
What an amazing lesson! Thanks L.
Really useful guide about percentages. Thanks!
understood . good teaching
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.
I'm very glad you like the videos!
Easy way to understand doing fractions or ratios algebra in simple English is to substitute the word 'of' for the divider line.
I like your explanations very much.
Can i have a formula to enter a date automatically when we enter data in the adjoining cell.
Videos are amazing. Watched you do this in IF functions and was slightly confused. Thanks for clearing up
Glad it's helpful.
It's a very nice trick to get result of percentage changes.
Thanks to sharing this video.
You're very welcome.
Gotta have the basics covered. Many thanks for the nice tutorial.
You're very welcome :)
I’ve improved my excel skillz thanks to this channel 👍🏼
Thank you very much for your support. I'm very glad to hear that.
Hi, thanks for your reply! If I can request for a tutorial it would be how to calculate payback period in a nice presentable way.
Hi Leila, could you please use your charming style of teaching and English, and explain audience the following: when the VAT increases from 20% to 21%, the increase is not by 1% but by 5%. Many people do not understand this, even editors in TV, newspapers etc. Thank you 😉.
THANK YOU SO MUCH Leila for this vidéo
Love you Leila. Many thanks for your video tutorial. I have improved my skill in ms excel.
Thanks for valuable information 🙏🏻
very helpful as an accountant ...
You are awesome and thanks for sharing such valuable tips
Thanks for the help
You are great. Your video really helped me.
I'm happy to hear that.
Smart trainer Leila and smart tricks♥️❤️
To be honest, if you're a financial analyst, or any kind of analyst for that matter, and don't master these basic concepts then you REALLY should not be doing your job.
On a side note, as always, great video again, Leila!
Thank you - Please how do I formulate to express a 100% increase or decline in the Sum column instead of getting #DIV/0! as the answer?
Thank you so very much
Your videos have been really really helpful
Thanks
My pleasure. I'm glad you like the videos.
again and again i wont stop thanking you :) love your videos
Always clear, Thank You
Thank you. That's what I was looking for. You're great.
very helpful, thanks
3:26 Yep. That is because Excel is expecting a decimal - and it converts the decimal (for example, your .05 example) into a percentage (%). Therefore, 30 is expected to be input as .30 which becomes 30% (multiplying it by 100). On the other hand, it also multiplies 30 by 100 and that’s why it returns “3000%” (3,000%). On the contrary, if you input the decimal beforehand - in comparison to formatting as a percentage (.05, for example) it is already the correct number and just needs to change the formatting.
Good stuff. How about irregular percentage increase in n years? Is there Leila version channel for Word?
Thank you, Leila! You are a godsend!
Basic indeed, but always nice to remember...
True :)
Simple but powerful
Thank you very much It helped me a lot. Thank You Thank YOu
Thank you so much for the explanation. I have seen the formula used but didn't understand.👌
You're very welcome Donna.
Been wondering how this was calculated for a while. Thank you!
You're very welcome Giacomo.
EXCELent, as usual!
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.
THANK YOU for this video. This is the help I needed to simplify my formulas.
You're very welcome.
Really very helpful thank you Ma'am
Most welcome 😊
Graduated from statistics major and the first thing I had to learn when hired was how to calculate % growth in excel.
:)
Thank you. Great lessons!