Excel/Grouping data, creating pivot charts
Hello Guys :) I am totally new in excel and need your help. I have several questions, which I need to be done for tomorrow. Please help me :
Given data like this:
CLIENT ID AggreementSignDate AggreementDueDate AggrementClosedDate
764938 1/4/2013 2/3/2013 1/3/2013
764971 1/4/2013 2/3/2013
765004 1/4/2013 2/3/2013
765067 1/4/2013 2/3/2013
765107 1/4/2013 2/3/2013
765152 1/3/2013 2/2/2013 1/3/2013
765159 1/3/2013 2/2/2013 1/3/2013
765168 1/3/2013 2/2/2013 1/6/2013
765169 1/3/2013 2/2/2013 1/3/2013
I need to: 2.1 Using columns "Agreement Due Date" and "Agreement Close Date" calculate how many days repayment was delayed or still delays (still delays if agreement closed date is empty)
A) Create summary table and group results by: 0 delay days 1-30 delay days 31-90 delay days 91-180 delay days 181+ delay days
B) Create chart to display this information
One of the biggest problems is that I do not know what to do when CloseDate is empty.
2.2 Tell us how many agreements have been to single customer. You need to create column "Total Loan Agreements" and put them in "Customer" sheet. A) How many customer have been more than 10 agreements? B) How many customer have been only 1 agreement?
2.3 If customer have been multiple agreements, you need to show which agreement is first, which second, third etc. A) In "Loan Agreements" sheet in new column display agreement sequence number B) Create summary table, that shows how many customer have been 1, 2, 3 etc agreements. You can group values together for better visualization.
2.4 Calculate in client level how many days is between first agreement close date and second agreement sign date. A) Show AVG days between first aggreement close date and second aggrement signe date. B) Create chart that display how many days are between first aggreement close date and second aggrement signe date. You can group days together for better visualization.
1-Without actually having a file to work with, this is tough to really know what you'reasking for.
2-"Single customer"? Your example has ONLY single customers (assuming Client ID = customer)
3-no way this can be resolved by "tomorrow" since I just got this today (Monday, 8/3) and I have no idea when this was sent.
4-This is not the type of questionone asks in AllExperts; this is beyond a "question"; it's more like "please do this homework assignment" or "please to this task I need to do for my job" request and is basically inappropriate, in my opinion.
5-this will require several back-and-forths, so again, no way you'll have this in time (in time for what?)
6-your topic says "...creating pivot charts", and you're totally new to Excel, so how do you know you want pivot charts (instead of regular charts)? Your data doesn't seem to warrant a pivot table in the first place anyway.
If you do want to pursue this, please send a file to me at email@example.com. Use subject of "AllExpertsQ". If not, ask another Expert.