• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate growth rates using dynamic name ranges

K

Kamarlon

New Member
My dashboard tracks monthly KPI data. I have a dynamic chart which changes based on the months selected. What I want to do is add a series showing the growth rate over the period. I know how to make the chart dynamic what I don't know is how to calculate the growth rates using name ranges. The name range for the series that is plotted on the graph is called "Y_Axis". So what I want is the growth rate of "Y_Axis". Thanks in advance for any assistance provided.
 
The growth rate would normally be the change in slope over the time frame. You could calculate this using the slope function and display the number(s), maybe using red / green for negative / positive.


Alternatively, you could calculate and plot the linear regression line for each KPI. You can do this by making an additional named range, or helper column, using the Slope and Intercept functions and your X_Axis and Y_Axis ranges using an equation like this:


=X_Axis*SLOPE(Y_Axis,X_Axis)+INTERCEPT(Y_Axis,X_Axis)
 
Mike, thanks for your response, but I think I need to explain some more using an example. Lets say the data for Y_Axis is as follows:

1

4

9

15


Then the first growth rate is (4-1)/1 = 3% the second is (9-4)/4)=125% etc, so its always (new-old)/old.
 
ok. You'd make the named range calculation sort of like this:


=IFERROR(((INDEX(Y_Axis,ROW($A$2:$A$5)-1)-INDEX(Y_Axis,ROW($A$1:$A$4)-1))/INDEX(Y_Axis,ROW($A$1:$A$4)-1))*100,#N/A)


Add a second axis to the chart and plot the named range calculated using the above equation.
 
Mike I used your formula but it's not working. I've sent you a link to teh file. Can you tell me what I'm doing wrong?


https://skydrive.live.com/redir.aspx?cid=16d0ed9ee1cb5b75&resid=16D0ED9EE1CB5B75!102&authkey=LyNV5hQzjSk%24
 
The formula isn't copied exactly. Look at the terms used in the Row() sections. Just copy the entire formula from above and paste it into the Name Manager.


However, this is now behaving a bit strange. If you just list the named range (Growth) across four cells, everything's happy. If you put this range in a graph, you get negative numbers. I'm going to need to do a bit of digging this weekend. (I'd be happy for an explanation, if anyone else is looking!)
 
Hi ,


I have the following data :


1 0%

4 300%

9 125%

15 67%


The range of percentages has been named Named_Range ; the graph displays correctly.


Can you say whether your situation is different from this ?


I am unable to open your Skydrive file on my computer , since Excel says it has been corrupted.


Narayan
 
Hi ,


See if the following link works :


https://skydrive.live.com/?cid=754467ba13646a3f&sc=documents&event_source=CloseButton&id=754467BA13646A3F%21102#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21103


Narayan
 
Narayan, I saw your file, but it's not quite what I wanted. The mathematics is correct, but I do not want to have to create a new series with the calculations. So imagine that my worksheet can only have one data series which is the actual values. I do not want to do the growth rate calculations in another column, I want to use named ranges.
 
Hi ,


OK. If I understand you correctly , you have a column / row of values ; you want to chart the growth rate of these values , without having to calculate the growth rate using your formula ? What does the named range contain ? Does it contain raw data values or the values of the growth rate ?


Narayan
 
The named range I currently have is the raw data NOT the growth rate. What I want to do is create a named range for the Growth rates.
 
Hi Kamarlon,


I think I know what you're trying to do. Try this:

1. Create a named range called "current". In the refers to, enter

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$24),1)

2. Create named range called "prior". In the refers to, enter

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$3:$A$24),1)

3. Create a named range called "GrowthRate". In the refers to, enter

=(current-prior)/prior

4. In the chart series values, enter

=[sheetname]!GrowthRate


That should dynamically add to the series as you add to column A. In the OFFSET formulas, I have selected A24 as being the last possible cell you can enter into, based on the fact that your draft worksheet had some assumption text starting in A25. But theoretically you could expand that range -- as long as both OFFSET formulas referred to the same COUNTA(Sheet1!$A$3:$A$##).


I hope this helps.
 
You must log in or register to reply here.
Back
Top