How to chart this data in Excel?

Factual Questions
1

So I’m trying to chart the average time for closing to purchase on 50 thousand some odd loans over the past year.

My data basically looks like this (but the loan numbers aren’t in any specific order):
Loan Number | Closed Date | Purchase Date
101…|4/15/12…|4/20/12
102…|4/15/12…|4/20/12
103…|4/15/12…|4/21/12
104…|4/15/12…|4/25/12
105…|4/16/12…|4/20/12
106…|4/16/12…|4/29/12
So basically I need a line graph showing the average by closing date, so I can see where the spikes are and how they’ve been changing over the past year.

For extra bonus, I need a graph with a different line for every investor where my data looks like this:
Loan Number | Closed Date | Purchase Date|Investor
101…|4/15/12…|4/20/12…|Investor A
102…|4/15/12…|4/20/12…|Investor C
103…|4/15/12…|4/21/12…|Investor C
104…|4/15/12…|4/25/12…|Investor B
105…|4/16/12…|4/20/12…|Investor A
106…|4/16/12…|4/29/12…|Investor D

2

I assume you have extra columns you arent showing - with “loan amount” for example?

For the first graph:

  1. Make a separate column with all possible dates (once)
  2. Use the “AVERAGEIF” function to find the average for that date
3

Oh - missed the part for “average time to closing”

step

  1. make a column and subtract column 3 from column 2

do the other steps I listed above

For graph 2 - ho many investors are there? If there are more than - say 10 - 20 - the graph you are looking for will be too cluttered to be readable.

4

Thanks, that was remarkably easy.
For Graph 2, It will only be for around 10 or so of the investors with the most volume.

5

They probably aren’t going to have transactions for all the days - which will look weird- here is what I would do:

  1. Make it by monthly instead. You can still do it by day if you’d like, but I’m including the extra step.

  2. To make it by month - use an extra column called “month”. Use the “MONTH” function which will give you a number between 1 and 12.

  3. Make an extra column that lists month one through 12. But start it on row 2. The reason why will become clear in the next step.

  4. Make ten extra columns and label them “Investor A”, B, C, etc - using the exact same spelling/capitalization as in the data.

  5. do not have a label for the column in 3 - the reason is it will confuse excel when you try to graph it.

  6. Use the “AVERAGEIFS” (note the plural) to average only when the month and the investor are the same.

  7. You need a formula in all 120 cels. Unfortunately when you drag the formula - it is going to mess it up unless you lock it as follows…

  8. Use the F4 key to cycle between locking the rows, columns, both, or none. This doesn’t make sense at first, but once you learn how to do this - you will become an excel master. In this case you want to lock the column for the month and the row for the investor in your 120 cell headings - you also want to lock the column in the day difference column, the investor column (in the orig data) , and the month column from step 1.

You can tell what is being locked by the dollar sign in the formula. It has no effect on the formula itself, but on dragging the formula.

Edited to add:

Step
9) almost forgot - highlight all 120 cells including the row and column headings - then select a line graph.

6

How can I average the data by month? I only have the dates for every loan. Really, if there was a way to average it by week would give me the best results.

Also, this might be more of a statistics question instead of an excel question, but what’s the best way to factor in volume? Right now the overall purchase time roughly matches the fluctuations in volume. I’d like to see if overall times are increasing or decreasing.

7

Use the function “WEEK” for week and “MONTH” for month - it gives you a number from 1:12 for month and 1:53 for week. You use the date as the input for that function.

Not quite sure I understand the second paragraph.

If you want a graph of the volume by Purchase or Close date - you can (again - probably summarize by month/week) use the “COUNTIF” function.

So make a column that gives you the week
Then make another column that is just 1:53
Then use the “COUNTIF” function for these columns

If data is more than a year - then you’ll likely have to use “COUNTIFS” to do both week and year. Or start it off using date only until you get comfortable.

Once you get good at it, you can literally do this in a couple minutes - I think I could do it in less than a minute if data was correctly fomatted to start with.

Learn to use COUNTIF/S - AVERAGEIF/S - and VLOOKUP as they are the mainstay of excel work - the AVERAGEIFS/COUNTIFS are a pain for beginners - as there are five inputs for one - four for the other, but once you learn it - as well as using F4 to lock the right cells - it will become second nature.

There are some pretty good YouTube videos as well - check out “Dueling Excel Podcast”.