Excel Curve Graph

I am trying to statistics on members and how long they've been a member.  I have not done much in the way of statistical formulas in Excel so I need some help.  Thank you!

What formula and/or graph would I use to create the following graph:
Retention Graph
Using the following data:
Retention Data
EdgeTodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Please could you post your data in an Excel workbook...

There seems to be errors (line 3 for example cannot join in May and leave April of '09)

Working off a picture is OK for little data, but not really for the data there.
0
CompProbSolvCommented:
I am not sure how much detail you want here.  Let me start with an overview.

(Your third line is interesting.  Someone joined in May and terminated in April of the same year?)

I would start by sorting on Duration. Lowest at the top.  This will be used as the data for the X axis.
I would add a column representing the % of entries including and below each row.  This is equal to the percentage of members who had not terminated.  This will be used as the data for the Y axis.

You would then create (insert) a Scatter Chart using the data as listed above.

If this is not enough detail, just ask for more.
0
SteveCommented:
OK, using the information you have there (after fixing point 3)...

I have plotted the points, used Excel to find the best fit curve
this gives a formula for the line of
percentage = (-0.237 x ln(months)) + 1.04 (*adjusted to reduce to no more than 100%)
(98.73% accurate in this case)

This can then be used to give the best percentages of those lines.

Months      LOG answer
12              46%
24              30%
36              20%
48              13%



See the attached workbook for the example working.

ATB
Steve.
Retention-Rate.xlsx
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

EdgeTodAuthor Commented:
Thank you so far.  I'm going to review this tomorrow morning and see if I can duplicate it using different numbers.

The data that I had was an example I found online and I used it to keep my question simple (sorry it was inaccurate).

The attached data is complete and real.  I'm going to try and duplicate your workbook using this data.
retention.xlsx
0
SteveCommented:
Based upon the data you have provided:

The curve plots as an exponential progression rather than logarithmic.
This gives a fitted curve with a 99.5% accuracy:
Percentage = (0.9774e) ^ (-0.091 x Months)

Months      Exp answer
12              34.4%
24              11.8%
36              4.1%
48              1.4%


The attached shows the workings for this result.

ATB
Steve.
retention.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EdgeTodAuthor Commented:
Thank you for running my real data.  This is looking great.  

If I were to put in different data, do I need to put in some sort of adjustment in the formula.  You seem to have done that on the first example you attached (+ 1.04 (*adjusted to reduce to no more than 100%)).

The formula Percentage = (0.9774e) ^ (-0.091 x Months) seems to have arbitrary numbers, do these need to be adjusted with different data? (I will end up running numbers for different periods)

Also, is there an formula that would show the % answer rather than the month answer (meaning showing 50% = 7 months rather than 12 months = 34.4%)?

Thank you!
0
SteveCommented:
The formula shown on the chart changes as the data changes.
This is the formula as per the Trendline of the data.
So new data will change the formula and r squared value displayed on the graph.

You should be aiming for the highest r squared value from the available trendline types.
The r squared is the coefficient of determination which is the percentage of the change in y value which is explained by changes in the x value.

To get the formula the other way round, you can rearrange the formula to make months the result, but this is a tad tough.
It can be easier to use Data > What if analysis > Goal Seek then use this to change the months to obtain the target result.
0
EdgeTodAuthor Commented:
I need to research the changes in the formula a bit more.  

It seems that the formula: =COUNTIF($C$2:$C$410,">="&C2)/COUNTA($C$2:$C$410) shows my chart data series without the need to adjust any formula.  The chart uses a trendline that needs no adjustment, correct?

The only change is if I want to measure the months in the separate formula (=EXP(0.9774)^(-0.091*H2)) that shows the Exp Answer, correct?
0
SteveCommented:
Correct on all counts.

The rearranging of the formula could be raised as a question in the maths section of EE. It may also benifit from being in the Excel topic too. Someone here will be able to re-arrage the formula to make the Months the result.

Just be sure to change the Exponent formula based upon the graph displayed formula and you will be fine (the formula is not linked to the graph label).

ATB
Steve.

PS:
On a side note... may I ask why only a "Good" grade was given?
Is there something from the original question which was not answered to your satisfaction?
0
EdgeTodAuthor Commented:
Steve,

Thank you for the follow up.  That helps further (and probably deserves a Great/A grade now).

The "Good" grade was given as I still had questions and had to explore further as to how the answer came to be.  I certainly do appreciate the help and in particular the additional comments that clarified further.

Tod
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.