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!
SUGGESTION: Your % change formula will not give the right result if the starting/old value is less than zero. You should use a combination of ABS() value, so if the old value is in B1 and new value is in A1, then the formula will look like (A1-B1)/ABS(B1)
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,...please do a video on escalation.....for example " £400 escalate 5% of price with 10% ,and 10% of price with 5%.....not sure what they want...😢
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 : Hi , please help me how to calculate difference between numbers with negative value. ( imagin columns actual and budget have negatives and positives numbers. how can I calculate changes ? thanks in advance . waiting for your response impatiently
How can you change the % every so many records? Ex Starting point (45% and i want it to incremental drop by 1% every $5000). So 45% @5000, 44%@10000, 43%@15000 etc....
Very very helpful. Now I have a situation. Try to help me please. 1. We have 50 students with random class admitted in different years 2. But from 30 family How can I easily group the siblings of each family. *Assume parent details mismatch* Hope I could make it clear to you 🙏🏻🙏🏻
@leila Hi Leila, I saw a video you did on how to convert currencies ie. USD to EURO but you used the exchange rate that’s build into excel. Can you please point me to the correct video again. 🙏🏻 Thank you in advance 🙏🏼
Need help on understanding this scenario. I get -300% growth if my old value is -5 and my new value is 10. That can be right? I should be seeing a possitive growth....not a negative growth. What am I missing here?
Can I suggest a video? The (NPV) Net Present Value function in Excel is really misleading (there are lots of web pages about this) because it DOESN'T calcuate NPV. It calculates the Present Value and leaves the user to do the net part. I bet a lot of people are misled by this and it would be good if you could explain it in your really clear way.
Good day Leila, I am one of your students on Udemy. Please how can I calculate the cost of goods sold if I have different prices for one product, e.g one product has a carton price, a dozen price, quarter price, and unit price. I will be happy if you can demonstrate that mam.
However, these formulas only seem to work if there are numbers in each cell. What happens if either cell, or both, have a zero value with results in a #DIV/0! error? Comparing the income AND expenses from one year to a next may result is increases, decreases or no change. I understand how to calculate a difference, but I have difficulty with the percentage formula if the dividend or the divisor or both are zero.
if Actual is a negative number like -2.16 and budget is 5.22 how do you calculate the percent of change or the percent increase or decrease between a negative and positive number. Do you have a video on this?
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??
Hi how are you doing I hope you are doing well I have a question for you I have a bag of emulsifier it is a 16 oz bag what is 1% of emulsifier can you let me know please in teaspoon amount or gram or ounce thank you in advance I really would appreciate your help
Leila is a Lefty, how nice. Remember, this percentage change calculation method has an implicit assumption: both the nominator and the denominator have to be positive.
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)
Is there an alternative way to find an isosceles right triangle symbol to replace the delta triangle? If anyone knows kindly share the way to get it. Thks
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..!!
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!
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
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.
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
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 😉.
Nice, how to calculate the original number know the percentage and the outcome from the percentage? I.e. 75 is the result of adding or subtracting out 20% of x. How can i find x?
For the sake of maintenance, I don't recommend using the x/y-1 formula in a large team situation. If you left the team, whoever is tasked with the job of maintaining your spreadsheet may get very confused with that formula as it's not intuitive for most people who are more used to the x-y/y formula. From a efficiency point of view, I doubt there'll be any significant difference between the two, but from a human readable point of view, the traditional formula is far easier to follow.
Thank you Leila Gharani for your video I have a question, please take a value(a) take 15%(b) of that value(a) and then subtract b from the value(a) and get a result(c) then take 15%(d) out from result(c) and then subtract 15%(d) from the result(c) and we need to do this in one formula please looking forward m sorry if I could not deliver it right =((100-(100*15%)-15%))
% change is always relative, normally it is relative to a known about or current amount for example column A is 2019 sales, and column B is 2020 sales then (B-A)/A will give you how much 2020 sales have changed relative to 2019. When thinking about margin it becomes trickier because you are calculating % relative to the final value (sell price) of an item. A lot of people make the mistake of adding a %. For example 10% margin on an item that costs $100 : 100*(1.1)=$110 which is wrong. You need to divide: $100/(1-0.1)=111.11 where 0.1 = 10% that way if you sell something that costs you $100 For $111 you make 10% of 111 because the 10% is relative to the SELL price and not your cost.
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!
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.
@ 2:56 pulling the formula down shows an extremely slow calculation. I have Excel 365 on a new laptop and Excel 2010 on an a much older laptop and I can say with no hesitation that Excel 2010 runs quicker. What I don't know is if it is because Excel 2010 is faster or my old laptop is faster. Does anyone else think the later versions of Excel run slower?
Great video as always. I'm suprised Excel doesn't have such a function like PCTCHANGE(initial, new) and use the math (new-inital)/ABS(inital) to handle numbers less than zero.
budget = what you expected, actual = what actually happened. So the meaning of her calculation is, how much smaller/bigger was the end result with respect to what was expected
Dear mam! i hve some data in two column (A date like 01-01-2010 to 31-12-2018 and in B column figures), i want to get monthly average after last date of month in rows through vba code, any trick for that?
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
Hey Leila, you are always making it so simply for all. I have an issue i think only you can help. i have a list of daha 100 cells vertically, all numbers. I need to to see if the numbers go from one point to another and drop %50 . Like 1 3 8 7 6 4 - price moved from 1 to 8 and back to %50 And if possible i want to calculate if it goes up after %50 and back down. I hope you see it.
Grab the file I used in the video from here 👉 pages.xelplus.com/percentage-change-file
This was good. Thanks. Could you do a quick video on the best way to calculate compound annual growth rate using Excel?
Why I did not find and marry you, my life would have been a lot easier 😊
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
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.
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
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?
SUGGESTION: Your % change formula will not give the right result if the starting/old value is less than zero. You should use a combination of ABS() value, so if the old value is in B1 and new value is in A1, then the formula will look like (A1-B1)/ABS(B1)
Yes. The ABS function works every time.
I learn something EVERY time I watch your videos. Thank you!!!
Good stuff. How about irregular percentage increase in n years? Is there Leila version channel for Word?
Thank you Leila. Other ways to do basic math formulas would be nice. You could make it a special series of videos.
Thanks I been calculating percentage for 20 years in excel, Never thought of this smart way to do it.
I use DAX all the time and love learning from your tutorials and not only that you are a beautiful intelligent lady!
Leila, you are my saver, You help me every time. THX. BR from Slovenia.
You explained that very well and understandably, Ms. Leila Gharani. I always enjoy watching your videos.
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,...please do a video on escalation.....for example " £400 escalate 5% of price with 10% ,and 10% of price with 5%.....not sure what they want...😢
Cool. Makes learning so meaningful. Thanks for sharing the tips.
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 : Hi , please help me how to calculate difference between numbers with negative value. ( imagin columns actual and budget have negatives and positives numbers. how can I calculate changes ? thanks in advance . waiting for your response impatiently
very helpful as an accountant ...
How can you change the % every so many records? Ex Starting point (45% and i want it to incremental drop by 1% every $5000). So 45% @5000, 44%@10000, 43%@15000 etc....
Very very helpful.
Now I have a situation. Try to help me please.
1. We have 50 students with random class admitted in different years
2. But from 30 family
How can I easily group the siblings of each family.
*Assume parent details mismatch*
Hope I could make it clear to you 🙏🏻🙏🏻
How to subtract percentage without writing the % number in any cell?
For Example - Subtract 15% from 220 without mentioning 15% in any cell ?
good news😅
ابجب وقتی راه راحت تر هست چرا واقعا چرا؟
=(A1/100)*B1*100
@leila Hi Leila, I saw a video you did on how to convert currencies ie. USD to EURO but you used the exchange rate that’s build into excel. Can you please point me to the correct video again. 🙏🏻 Thank you in advance 🙏🏼
Need help on understanding this scenario. I get -300% growth if my old value is -5 and my new value is 10. That can be right? I should be seeing a possitive growth....not a negative growth. What am I missing here?
Can I suggest a video? The (NPV) Net Present Value function in Excel is really misleading (there are lots of web pages about this) because it DOESN'T calcuate NPV. It calculates the Present Value and leaves the user to do the net part. I bet a lot of people are misled by this and it would be good if you could explain it in your really clear way.
Good day Leila, I am one of your students on Udemy. Please how can I calculate the cost of goods sold if I have different prices for one product, e.g one product has a carton price, a dozen price, quarter price, and unit price. I will be happy if you can demonstrate that mam.
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!
How do you calculate % variance when one number is negative? Budgeted profit but actual loss. (A-B)/abs (B) does not always work.
However, these formulas only seem to work if there are numbers in each cell. What happens if either cell, or both, have a zero value with results in a #DIV/0! error? Comparing the income AND expenses from one year to a next may result is increases, decreases or no change. I understand how to calculate a difference, but I have difficulty with the percentage formula if the dividend or the divisor or both are zero.
How to compute voters turn-around? Meaning list of voters=550 person, only 320 person votes, what is voters turn-around % ?
Mam How We add Data Analyze in Home Tab Please guide me by video it is special feature MS Excel Version
No deberías explicar temas en los que no estás muy segura. Se nota tu inseguridad al exponer un poco de álgebra.
in fairness, this is grade 3 maths in most countries. Certainly Scotland in the '70's. Probably Kindy in China, Japan and S Korea
if Actual is a negative number like -2.16 and budget is 5.22 how do you calculate the percent of change or the percent increase or decrease between a negative and positive number. Do you have a video on this?
I need the basic cell 1 example is 100$ cell 2 needs to be 13% of cell one to equal say $13.00
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??
Wow
Hi how are you doing I hope you are doing well I have a question for you I have a bag of emulsifier it is a 16 oz bag what is 1% of emulsifier can you let me know please in teaspoon amount or gram or ounce thank you in advance I really would appreciate your help
Leila is a Lefty, how nice.
Remember, this percentage change calculation method has an implicit assumption: both the nominator and the denominator have to be positive.
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)
Why there's no built-in function in Excel to calculate the Percentage Change, as in some calculators?
Is there an alternative way to find an isosceles right triangle symbol to replace the delta triangle? If anyone knows kindly share the way to get it. Thks
When I write 10 in a cell and press % on the ribbon bar my 10 changes to 1000%. How to fix it so that it shows 10% 🙏🏼
THANK YOU SO MUCH Leila for this vidéo
In one of your Formula eg. =A3*(1+B3) what is that "1" for not clear ??
Hi am watching your videos. Please can you help me increase shs 2000 by 30%
Easy way to understand doing fractions or ratios algebra in simple English is to substitute the word 'of' for the divider line.
can we have % diff for 3 deimnssions like Actual vs Budget vs Forecast
You haven't shown the percentage decrease which is very important for people working in sales.
I'm taking your course and you've lost me there and here by the use of '1". I'm not sure how to reach you with that question.
restaurant pnl , budgetting , forecasting in excel ....i mean financial modelling
I like your explanations very much.
Can i have a formula to enter a date automatically when we enter data in the adjoining cell.
Thank you!
is there a built-in function for the percentage change? im using pro plus 2013
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..!!
if figure we are comparing with is in negative and actual is in positive ,then this will show the difference in negative ,which will not correct.
Thanks Leila! I teach statistics and very happy to see your lecture!
Thanks for valuable information 🙏🏻
Thank You Very Much Leila Gharani I like your video very much
Ree-port 😂…thank you for all the great videos. 🙏👍
How do you do this formula to show the negative number is in red and positive number is in green
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.
always love you. i bought your course on udemy and it's really useful for my work. thanks for always update new trick.
How can i calculate average percentage of compound interest?
Thank you very much It helped me a lot. Thank You Thank YOu
get rid of the music, to loud and ever powering. had to stop watching
Thanks from Iran.
What did she say? Too busy watching the figure 💕
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!
Really useful guide about percentages. Thanks!
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
Basic but lovely
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
Does this work Actual/Goal?
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 😉.
Nice, how to calculate the original number know the percentage and the outcome from the percentage? I.e. 75 is the result of adding or subtracting out 20% of x. How can i find x?
75 / 1.2 = 62.5 and 75 / 0.8 = 93.75
Thank you I liked that . I can use it for my students.
Great!
For the sake of maintenance, I don't recommend using the x/y-1 formula in a large team situation. If you left the team, whoever is tasked with the job of maintaining your spreadsheet may get very confused with that formula as it's not intuitive for most people who are more used to the x-y/y formula. From a efficiency point of view, I doubt there'll be any significant difference between the two, but from a human readable point of view, the traditional formula is far easier to follow.
Thanks for sharing your view on this!
EXCELent, as usual!
I swear this was my exam for Analyst administrator
Thank you Leila Gharani for your video I have a question, please
take a value(a) take 15%(b) of that value(a) and then subtract b from the value(a) and get a result(c) then take 15%(d) out from result(c) and then subtract 15%(d) from the result(c) and we need to do this in one formula please looking forward m sorry if I could not deliver it right
=((100-(100*15%)-15%))
% change is always relative, normally it is relative to a known about or current amount for example column A is 2019 sales, and column B is 2020 sales then (B-A)/A will give you how much 2020 sales have changed relative to 2019. When thinking about margin it becomes trickier because you are calculating % relative to the final value (sell price) of an item. A lot of people make the mistake of adding a %. For example 10% margin on an item that costs $100 : 100*(1.1)=$110 which is wrong. You need to divide: $100/(1-0.1)=111.11 where 0.1 = 10% that way if you sell something that costs you $100 For $111 you make 10% of 111 because the 10% is relative to the SELL price and not your cost.
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!
Hi
Big fan of your stuff
Can you make a course for financial analysis
It doesnt work in my excel Windows 10
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.
@ 2:56 pulling the formula down shows an extremely slow calculation. I have Excel 365 on a new laptop and Excel 2010 on an a much older laptop and I can say with no hesitation that Excel 2010 runs quicker. What I don't know is if it is because Excel 2010 is faster or my old laptop is faster. Does anyone else think the later versions of Excel run slower?
Great video as always. I'm suprised Excel doesn't have such a function like PCTCHANGE(initial, new) and use the math (new-inital)/ABS(inital) to handle numbers less than zero.
1:02 Huh? I would do (Budget - Actual) / Actual. I always learned to compare against what actually happened.
budget = what you expected, actual = what actually happened. So the meaning of her calculation is, how much smaller/bigger was the end result with respect to what was expected
you like numbers? you want mu numbers he he
There is one vlogger . Its channel is innorative. He discussed computing growth. This his formula =(current-previous)/current
Dear mam! i hve some data in two column (A date like 01-01-2010 to 31-12-2018 and in B column figures), i want to get monthly average after last date of month in rows through vba code, any trick for that?
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
Hey Leila, you are always making it so simply for all. I have an issue i think only you can help.
i have a list of daha 100 cells vertically, all numbers. I need to to see if the numbers go from one point to another and drop %50 .
Like
1
3
8
7
6
4 - price moved from 1 to 8 and back to %50
And if possible i want to calculate if it goes up after %50 and back down.
I hope you see it.
Was Always looking for this concept.. Finally found it. Thanks for teaching elemental basics and then excel!