Your question, your audience. Choose who sees your identityâ€”and your questionâ€”with question security.

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.

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

I have plotted the points, used Excel to find the best fit curve

this gives a formula for the line of

(98.73% accurate in this case)

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

12 46%

24 30%

36 20%

48 13%

See the attached workbook for the example working.

ATB

Steve.

Retention-Rate.xlsx

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

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!

This is the formula as per the

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

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

It seems that the formula: =COUNTIF($C$2:$C$410,">="&

The only change is if I want to measure the months in the separate formula (=EXP(0.9774)^(-0.091*H2))

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?

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

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.

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 answer12 34.4%

24 11.8%

36 4.1%

48 1.4%

The attached shows the workings for this result.

ATB

Steve.

retention.xlsx