Line chart for churn rates of many items over time

[Whoops]

I have a set of data that tracks the number of changes to a file over
time. I want to show a line chart that I can use to visually locate a
file with a low churn rate. "Low churn rate" could either mean "Has
changed a lot in the past but hasn't changed in a while" or it could
mean "has the lowest number of changes happening to it right now."

The purpose of this is that we have a set of operations we want to
perform on files, but only if they are fairly stable and either aren't
getting changed often, or haven't changed in a while.

My data is something like this:

FileID,ChangedDate
1,8/23/2005
1,8/24/2005
1,8/25/2005
2,8/23/2005
3,8/23/2005
4,8/23/2005
4,8/24/2005

I'm having trouble wrapping my mind around how exactly I want this to
look graphically. I definitely want the X axis to be time and I want
one line in the chart per FileID, but I'm not sure about how to
configure the Y axis to reach my goal of allowing me to visually pick
out a "low churn" file.

If I were to just have "Count of changeddate per fileid" as the Y axis,
then all I get is a flat horizontal set of rows which just tells me a
file has churned a certain amount, it doesn't tell me if it's churning
less now than it was a month ago.

If I make it a bar chart with the X axis of FileID and the Y axis
ChangedDate, with the data portion being Sum of FileID, that *kind of*
gets me what I want, because you can visually pick out the FileIDs that
have a lot of bars next to them. The problem with this is that I expect
to have hundreds if not thousands of FileIDs at some point, so I need
something to help me visually pick those out... and perhaps a line
chart isn't it, but it seems closer than anything else.

Any pointers much appreciated, thanks.

 

[Tushar Mehta]

How about the metric "number of changes in the last N days?"

How would one implement this? Visually, yes. In a chart no. Suppose
your data set is in columns A:B starting with the headers in row 1.
Then, in C1 enter the text:NbrChanges in
In D1 enter a number, say 2.
In E1 enter th text:days

In C2 enter the array formula
=SUM((N(OFFSET(A2,-ROW(INDIRECT("1:"&($D$1+1)))+1,0,1,1))=A2)*(N(OFFSET
(B2,-ROW(INDIRECT("1:"&($D$1+1)))+1,0,1,1))>=B2-$D$1))

What this does is look at the last N entries (the value in D1) and
counts those that refer to the current file and have a date value
within the range specified by (current date less the value in D1).
This gives you the number of times the current file was changed in the
period defined by the value in D1.

Now, use conditional formatting to highlight those entries with a low
(or high) churn rate. This is the visual indicator of low/high churn.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Whoops]

Thanks Tushar, I'll give this a shot.

 

[Tushar Mehta]

Thanks Tushar, I'll give this a shot.

You are welcome. If you get a chance, do post back how it works out
for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions