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
Who is Participating?
SteveConnect With a Mentor Commented:
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.

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.
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.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

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.
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!
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.
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?
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).


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?
EdgeTodAuthor Commented:

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.