Help! I need to do a data analysis via excel using specific data "stuck"

[michaelaz21]

I'm a beginner/intermediate user of excel (in certain areas)....

I currently work in the motor industry & have 3 columns that I need to analyse to work out our service retention.

I have:

Column I: Delivery Date (e.g. 01/01/2011)
Column J: Date of last work (e.g. 01/01/2012)
Column K: Next Service Date (e.g. 01/01/2013)

I currently have 3 separate work sheets which range from: 12, 24, 36 months.

Not sure where to start of how I would go about doing a data analysis for retention

Please help me!!!

[humdingaling]

are you able to provide example of what you have and what you want
i am having trouble picturing it

delivery date is initial
last (latest service?) work is the most "current" so effectively delivery date and last work can be the same?

[michaelaz21]

RETENTION.JPG

I hope you can see the attached image of an example?

With service schedules for new vehicles, we normally advise our customers to get a service done every 12 Months (roughly) from their purchase date.

The delivery date is the actual delivery of our New Vehicles.
The Date of Last work - Is the last visit they made to get their scheduled service done.
The Next Service Date - Is the next date that they're due for another service.

In my example below:
Column I: Delivery Date (e.g. 01/01/2011)
Column J: Date of last work (e.g. 01/01/2012)
Column K: Next Service Date (e.g. 01/01/2013)

The vehicle was delivered on 1/1/2011
Date of their last work/service was 1/1/2012
The Next Service Date is 1/1/2013

So from the example above - This tells me that they bought their vehicle on: 1/1/2011, they had their scheduled service on: 1/1/12 BUT... because their next
service date is still sitting on: 1/1/2013 (This tells me that they didn't return to us because if they had - the next service date would've changed to: 1/1/2014).

So - This is the type of data I want to analyse to see what our service retention percentage would be.... i.e. what customers have returned for their scheduled services & what customers haven't.

I have 3 separate worksheets as mentioned (12 Mnth Customers, 24 Mnth Customers & 36 Mnth Customers) - We need to know how much of these customers have returned for regular servicing & which ones haven't)

We do deal with an external company BUT they're too busy to help me. I do have the last retention report they ran for us (if that may help you?)
If you think you can help - I'm more then happy to provide you with email address so I can correspond this way???


Cheers
Michaela

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info)

[michaelaz21]

The file I inserted was an example & NOT the actual file I'm working with?

[michaelaz21]

Furthermore - I have had a look at it again & can see I am able to insert a table... I'll do it this way in future. (First time user - still getting use to everything).
The information that I included actually applies to all makes/models of any motor industry. So I wouldn't class that as sensitive info I would've thought?

[FDibbins]

My comment was mainly directed at the pic you uploaded. Pics are pretty much impossible to edit/work with, and most members are dis-inclined to retype your data for you

[michaelaz21]

Thank you FDibbins

This explains a lot. I haven't had much luck with it.
Where's an excel Guru when I need one? lol.

I have soooo much to learn about this forum (#feelinlikeAtotezLoser)hahaha

[FDibbins]

if you upload your sample workbook, I will take a look for you

and as far as learning from the forum, that takes a bit of time (not much), just hang in there with us, we are here to help

[michaelaz21]

EXAMPLE_WORKBOOK.xlsx

Please tell me I did it right?
Example work book attached.

Again - Wanting to know how to analyse the data to work out our retention.

[jubiesxl]

Hope this helps
in Cell E2 of April 11 to March 12 tab, insert the formula
=IF(D2<=TODAY(),"Not Retained","Retained")
and copy down to as many rows required

In Cell F2 insert the formula
=COUNTIF(E:E,"Retained")/(COUNTIF(E:E,"Retained")+COUNTIF(E:E,"Not Retained"))*100
and copy down

let me know if this works

[michaelaz21]

jubiesxl - thank you so much the first part worked BUT... when I tried to do the 2nd part & copy down - it shows the same value as F2. Do I need to copy down?
Would you be able to upload an example workbook that I could follow?

[humdingaling]

% retention for each sheet
you probably shouldnt do column F all the way down
as it will give the same answer all the time
which is count retention for whole sheet divide by total clients (as percentage)


have placed that code into spreadsheet and only included 1 line (row 94) as your %
isn't this the data you want?

[michaelaz21]

Yes this is exactly what I was after. Sorry for my ignorance.
Thank you so much for your help!

I can finally close this one off as [SOLVED] - YAY!!

[jubiesxl]

Sorry, misunderstood the question from OP.

Thanks for clarifying humdingaling

[humdingaling]

thats ok you did all the hard work