Excel offers us multiple configuration options. As office automation tool allows make thousands of calculations just minutes simplifying work tasks of the day. Thanks to Excel tasks like calculate the annual growth rate is very simple to do with formulas percentage .
How calculates the annual growth rate in Excel?
Calculate the percentage growth are automatically and a very practical way only with the application formulas. There are many ways to do the calculation, but this is the easiest to apply formula.
- % Growth = ((current value-value previous month)/(previous value)
is the applied formula href=”https://products.office.com/es-co/excel” Excel the cell where want to show the results of the operation will be performed percentage.
- = ((B3-B2)/B2)
B2 represents the value obtained from the previous month, ie the previous cell to the month being evaluated. Cell B3 represents the current balance, which is to assess the percentage of growth.
Note: so you can view the value of the percentage in the cell, you must change the format to percent. Right click on the cell, select the cell format and finish the percentage selected category.
alternative Formula (XIRR or function XIRR)
is usually formula is used to assess the value flow box periodically . The meaning of its syntax is XIRR and means “valores.fechas.estimar.” If the Excel version is in English, then the function name is XIRR.
- Values: is the range of data used to measure the rate
- Dates: evaluation period data li>.
- estimate : is optional and is used to find an estimated value that approximates the TIR
You may also be interested in:
All aggregated values must have a minus sign (-) to the end so that the calculation is done successfully. Applying this formula in Excel would thus be:
- = XIRR (B2: B13, C2: C13)
Where B represents the value column and the C column represents the date
Finally, one can calculate the average of the results obtained Compare the result of a month with other values Monthly . It is available only agree predetermined function and her writing the word and placing average values to calculate.
- = AVERAGE (B2: B13)
Calculating the average of each month in relation to the total
Once you have calculated the total sum of all values to be evaluated can do calculation of the percentage of sales made in that period of time relative to the entire year . The formula is as simple as that used for calculating the average. the annual sales total is divided by the corresponding overall total sales for that month.
- = B2/$ B $ 14
Note: The calculation formula is simple sum = Sum (B2: B13). What is indicated in formula is that it should make the sum of all values between B2 and B3.
Add icons graphics
display the results in a more graphical form and that is understandable to the naked eye, you can add a colored icons in an arrow . Each arrow has a meaning, if green and upwards means there was a growth in sales, if this yellow were no changes from the previous period and if this Red and down means there was lost.
To apply this change is necessary add another formula in another cell . The result of the formula is 1 = only were growth 0 = stays the same and there were losses -1 =. You must start from the second period, because the first month has no comparison.
- = sign (B3-B2)
Then select the result obtained in all cells and from the Home tab, click on the Conditional Formatting Select the icon set you prefer and. changes will apply immediately.
You may also be interested in:
To remove the numbers on the side of the icon you change the rules of the format , from the same option you can manage conditional formatting rules. Select only display symbol, and apply the changes and everything will be perfect.
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?