• 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.

Percentage Change, Moving Avg Year Over Year Calculations Assistance Request

J

JMS

New Member
Dear Fellow Chandoo.org Users,


I have pasted in the link to an Excel 2007 file below. I have tried to follow posts for formulas etc on this website and I need a compass check from others with more experience. The situation I face is that the count of status and average number of days can change every day due to cases being completed and that will change the average number of days in the the calculations for the month. A case can change status about six times as it moves through a process. I have posted only completed cases, however today a case from 2 years ago can move into this closed case category and the statistics change.


I need to know if I have calculated the percentage of change, moving average and also the year over year change calculations correctly. Is there a better way to do these calculations and perhaps automate the process somewhat?


Any assistance or suggestions would be greatly appreciated!


Sincerely,

JMS


http://dl.dropbox.com/u/62453354/Spreadsheet%20for%20Chandoo_org%20Upload.xlsx
 
Hi JMS,


Firstly welcome to the forum.


In your sheet there are two table adjacent to each other, both have similar values.


Kindly clarify following points:


-If a new case is entered then it will change the Count and avg. days

-Will you always be calculating things for 12 months period or it will also change.

-Apparently your sheet seems to be working OK, what are you suspicious about it.


Regards,

Faseeh
 
Hi ,


I think you need to first write down what exactly you wish to do in each of the calculated columns.


Can you write down what kind of calculation should be done to get each of the columns ? Only after the calculation is written down should the implementation using an Excel formula be done.


Just to give an example , the formula for calculating the 12 month moving average is entered as :


=AVERAGE(OFFSET(C2,COUNT(C2:C14)-12,0,12,1))


The breaking up of this formula into its components gives COUNT(C2:C14)-12 as equal to 1 , which means you are starting from cell C3 rather than C2 ; is this what you want to do ?


Narayan
 
Dear Faseeh,


Thank you for a welcome to the forum, I am learning a lot about Excel here on the website and want to apply the technics here to better calculate and illustrate my data. There is so much information here that it can get overwhelming.


Yes, as the case moves through the process the count in each step of the process changes as well as the average number of days. An example when when a case moves moves through one four step process different people at step (draft, review, review, review and decision [same person])the count and average number of days will change as the case is processed. I want to track how many cases are at each step in the process and average number of days for case in that step. I have a spreadsheet with over 26,000 cases that I created a pivot table to give me the number of cases (count) and average number of days for the case during the month/year. I then took the results, depending on the step in the process and copied the results to a separate spreadsheet page like the one I posted. From there I can create a chart that shows the count and average number of days for that particular step.


I finally have enough data to show a year over year comparison in a chart. It would be nice to be able to show by quarter also, but I wanted to see if the 12 month comparison worked before I tried calculating quarterly statistics.


This was my first time trying to calculate the moving average and percentage of change month to month, which I discovered on Chandoo.org and I wanted to be sure I was doing it correctly before I send the charts to the boss. Please see response to Narayank991's post below for where think I am incorrectly doing a calculation. Thank you sincerely, for your looking at my spreadsheet and asking questions. :)


Dear Narayank991,


Columns A, B, C, D, I and J all come from a pivot table that calculates the number of cases (count) and average number of days for cases created during that month from the over 26,000 cases. I should have deleted Column D, I have that there to give me the data for a chart and it is a repeat of Column I, I apologize. My charting skills are still developing and I haven't been able to draw data from different columns and rows to create a chart so for now they are adjacent to each other to facilitate charting.


Column E is the 12 month moving average for Count of Status:


=AVERAGE(OFFSET(C2,COUNT(C2:C14)-12,0,12,1))


Column F is the percentage of change in count calculated from the previous month:


Example: =(C4-C3)/ABS(C4)


Column G is supposed to be a calculation of percentage of change for the year and this is where I run into trouble.


Example: =(E14-C2)/ABS(E14)


I took the 12 month moving average (E14) and subtracted C2 (the Oct 09 count), but I don't think that truly relflects the 12 month moving average. Should I have summed the Count of Status C2:C14 and then tried the rest of the formula? When I did that it kept giving me an error, so I posted my spreadsheet. I don't know how to do this type of formula.


Columns K, L, M, N repeat the process for Average number of days using the same formulas.


When I chart the count of status and average number of days I have a sub-chart that I want to put in that shows the percentage of change year over year, that is why I want to calculate this data.


In your post you noted the following and asked the question:


"=AVERAGE(OFFSET(C2,COUNT(C2:C14)-12,0,12,1))


The breaking up of this formula into its components gives COUNT(C2:C14)-12 as equal to 1 , which means you are starting from cell C3 rather than C2 ; is this what you want to do ?"


Answer, I did not realize that the formula above did not include cell C2, which I want to include. Should I change the digit after the last comma to a 0 (zero) to include cell C2?


I sincerely appreciate your viewing my spreadsheet and asking questions, you made me think more deeply about what I am trying to accomplish. :)


JMS
 
Hi JMS,


Glad to see your reply :) . Let me clarify one point and that is what is moving average. In Moving average we add a new Month and delete the last one in a set of observation. For an example in your case if we had been calculating the moving average of 12 months starting from Nov, 09, we would have averaged Nov, 09 to to Oct, 10 (making 12 months), and averaged them.


Now when it comes to second turn, we would have exclude the month of Nov, 09 and added Nov, 10, making it again 12 month and 'a moving average' for the month of Nov, 10.


In your case, that is not moving average, rather simple average. Because you average month from Nov, 09 to Oct, 10, then from Nov, 10 to Oct, 11 so that is a case of simple 12 month average!


Now when it comes to calculating average, there is no need for such a long formula, because what it does in end is simply averaging 12 months? Am I Correct?


Your formula for calculating average of 12 month (that you are referring to as moving average, actually it is not) is correct. Same is the case with your 'change between period', your formula is correct.


In Column D, your formula calculates the percentage monthly change that is also correct. I think you were more dubious regarding the mathematical concept of these formula then their excel-workout? But BTW both are correct! If there is any thing else you are worried about, you may revert any time. :)


Regards,

Faseeh
 
Jumping in late, but it looks like a portion of the problem is with the use of the OFFSET function. I shall try to explain. The arguements in OFFSET are:

=OFFSET(Reference,Row_Shift,Column_Shift,#ofRows,#ofColumns)

Of which the last 2 are optional.


Examples:

=OFFSET(C2,1,1)

This refers to cell D3 (1 row down, 1 column over)


=OFFSET(C2,-1,0)

This refers to cell C1 (1 row up, no column shift)


For a dynamic, chart, usually you make it dynamic via manipulation of the last 2 arguements. Since you stated that you always want to include C2 (which is also being used as the reference point, I'm guess that you don't want any shift.


That said, the COUNT is, I'm guessing, letting you know how many cells actually have data in them. In which case, your formula would need to be:

=OFFSET(C2,0,0,COUNT(C2:C14),1)


This says to start at C2, have no shift, but return an array with a size equal to the number of cells that have a number, aka, the COUNT. So, if C2:C4 have numbers, the COUNT function evaluates to 3, and the OFFSET function will in turn refer to C2:C4.


As a FYI, sometime formula writers will omit the 0 in an argument, making the formula look like:

=OFFSET(C2,,,COUNT(C2:C14),1)

Both are valid, and it is a matter of personal preference.


Anyway, hope that helps a little.
 
Hi JMS ,


I think you might have already got the solution to your problem ; I would just like to add to Luke's explanation ; if you are using a 12-month moving average , then COUNT(C2:C14) will result in 13 , which is not what you want.


Secondly , the use of the word "moving" means that you have to have repeated calculations ; as Faseeh has already mentioned , if it is just one calculation over one 12-month period , then it is only an average , not a "moving" average. To elaborate on this a little , if you have data in the range C2:C15 , then you can calculate a 4-value moving average of the data in C2:C5 ; thereafter , you need to have formulae in subsequent cells to calculate the averages of data in C3:C6 , C4:C7 , C5:C8 and so on.


Lastly , suppose we take the same example I have given above , of a 4-value moving average ; the first calculation will be =AVERAGE(C2:C5) ; if you copy this formula downwards , Excel will automatically ensure that the formula changes to =AVERAGE(C3:C6) , =AVERAGE(C4:C7) ,... The use of OFFSET and COUNT may not be required.


Narayan
 
Dear Faseeh, Luke, and Narayan,


I really appreciate your input, suggestions, and explanations! You all refocused my efforts and reminded me of the KISS principle (Keep It Simple Stupid). Thank so very much, I am on track and have been reworking the spreadsheet and now I am working on the charting. My confidence is building and I will continue to use the site and as need the forum. My best wishes to the three of you for answering a call for help.


Sincerely,

Jim
 
You must log in or register to reply here.
Back
Top