You are here:

# Excel/Grouping data, creating pivot charts

Question
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 bobumlas@hotmail.com. Use subject of "AllExpertsQ". If not, ask another Expert.
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment No Comment

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Bob Umlas

##### Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

##### Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks