Appraisers' Free Forum

Dates are tricky little suckers. The spreadsheet holds them as serial numbers representing the number of seconds since some arbitrary starting date. In this form, they are almost worthless to humans. So spreadsheets have several functions that work on them, not the least of which is cell formatting

When you enter a date in a cell, the sheet displays what you entered. Thus, 3/1/06 is displayed just that way. But the sheet stores this date as a serial number. You can change the format of the cell by setting the formatting to something like "MMM DD, YYYY" and that same date will suddenly display as "Mar 06, 2006". But the serial number will be the same. Remember that.

In my rural market, sales happen slowly. I wanted to show the number of sales per period, but didn't quite know how. I had downloaded three years' worth of sales data from my provider, and had imported it into a spreadsheet. One of the columns was "Last Sale Date." There were 123 sales shown.

"Aha! I can get that thing to display only the year and the quarter!" I thought. Selected the column for Last Sale Date, inserted another column, and labeled it "Quarter." Selected the first cell, and entered "=S2" to pull the date of sale over. Formatted the cell to be "Q YY." Sure enough, I got Q1 06 next to the first cell. Quickly copied it down. Now I've got a whole column showing sales in the same quarter.

So I ran a Pivot Table to aggregate these sales by quarter. Selected a cell underneath the table at about A130, and activated the Pivot Table wizard (It's called a 'data pilot' in OpenOffice Calc.) Dragged the "quarter" column into the Data Fields section, double-clicked it, and set "count" as the aggregating function. Moved "quarter" into the row header section so I'd know what quarter I was looking at the aggregate of. Ran the Pivot Table, and got garbage: 121 lines, all saying the same thing they were in the main table. Only two days had more than one sale recorded on them.

What was happening was that Excel was simply aggregating entries which occurred on the same DAY! (Because the sheet keeps track of dates as serial numbers.) Not the result I wanted at all.

Fortunately, there's a way to make Excel (or Calc) quit messing with the date format. Use the TEXT() function. I set the first cell of "Quarter" to the formula =TEXT(S2, "Q YY"), and copied everything down the column.

This time, the Pivot Table worked. Well, almost. What I got was a pivot table which read:
Q1 04 7
Q1 05 9
Q1 06 15
Q1 07 10
Q2 04 ...
...
Q4 06
Q4 07

Again, not what I wanted at all. I had wanted Q1 04, Q2 04, etc. So I went back and changed the formula to =TEXT(S2, "YY Q"), and copied that down. When I ran the pivot table this time, it gave me the answer I'd been looking for. You get two columns of output. The left column is the quarter, the right is the number of sales. Select'em both, and insert a chart. Here's what it looks like:



Most of you work in more active markets, so you may want to aggregate by month. Just use =TEXT(S2, "YY MMM") to capture the month in which the sale occurred, and use the pivot table to aggregate the sales. You should produce a table which shows you the number of sales in each month. If you're not going back into the previous year, then just use =TEXT(S2, "MMM").

In order to view the chart full size do I have to download a program? All I get when I "click here" is some instructions and options.

I fixed it to be an inline graphic.

Thank you too much. Now I shall try and follow your instructions. Do you have some friggin' book or something?

Yep. It's the Help file and Google. Also picked up some tips from Mr. Excel. (Bill Jelen's got some neat tips.) But Google is your friend for specifics, and I happen to think it's better than the built-in Help file for initial learning.

After you've used a function a couple of times, the Help file is a handy reference because you already know how all that stuff works. Just need a memory nudge.

Jim Plante wrote:Yep. It's the Help file and Google. Also picked up some tips from Mr. Excel. (Bill Jelen's got some neat tips.) But Google is your friend for specifics, and I happen to think it's better than the built-in Help file for initial learning.

After you've used a function a couple of times, the Help file is a handy reference because you already know how all that stuff works. Just need a memory nudge.

My problem with googling. What's the question?

What do you want to know? Just ask the question the same way you do here.
"How do I use a pivot table?"
You could try to shorten it to "pivot table in Excel" maybe, but why?

But sometimes you just have to synthesize what you already know, like when I used the Text function to collect those dates into quarters.

Wow, too cool!

Thanks Jim!!!!!

I'm slowly learning. This looks difficult .... but when I have a few extra hours (days) I'm going to try this.



Yes, I would want it by the month in my area. I have over 2 - 3k sales in a one year period. I would also want to sort by zipcode or county. I'm finding the zip thing very hard to deal with trying to graph. Feels like I'm back in the second grade.

Rhonda,
Subsetting data is sometimes a PITA. Here's how I'd do it.
Select the whole data set and sort by zip code. Only somewhere in that sort dialog there should be an option to copy the sort results somewhere else. If you make yourself another empty worksheet before sorting, you can copy the results sorted by zip code to that sheet.

Then, change to your new sorted sheet, Select the zip codes you don't want, and do "Delete rows."

Alternatively, you can select the zip code you DO want to keep, and copy/paste it into yet another workbook sheet. Now you can do the date thing, just working in that (those) zip codes.

BTW, right-click on the sheet tabs to bring up a context menu that will allow you to rename them.

Thanks

Yep, it will be a PITA.

I'm sure I'll have problems ... I'll be back. I vaguely remember doing spreadsheets about 25 years ago and I'm not sure why my brain doesn't work anymore.

Getting the zip code alone may be problematic if your MLS reports zip+4.

The problem is that an entry such as 90210-1405 is interpreted by Excel to be text, because it has a text character in it (the hyphen.) So use a text function to break out the larger part of the zip code.

To do that, add a column to the right of the zip code. In the first cell of that column, enter =left(J2, 5); copy it down.
That'll pick off the first five characters of the zip (assuming it's in column J). Then you can use the pivot table to aggregate the sales by zip code.

Wow Jim ... how do you know all this stuff?

I wish you lived closer and I'd come pay you for lessons. It's much easier in person.

I'll be practicing again very soon - as soon as I get this BIG headache order out around midnight.

Rhonda,
You'll learn just as well, and more permanently, doing this the way you're doing it. Learn a new trick; apply it; modify it; cuss 'cause it didn't work; try again; ask for help; and work it out.

A question asked in private educates one person. One that you ask here will educate over a hundred (so far).

I don't mind your dropping by for adult beverages and singing dirty songs to my neighbors. But spreadsheets have little use during happy hour, which can last more than an hour.

Jim,

I am working with an OFHEO table that lists appreciation (over/above the last qaurter0 by quarter since the 1970s. So there are three columns. When I bang the chart (scatter plot or anything else) I get junk. How do I get Excel to show me years, quarters and apprection. I'd also like to show appreciation from same quarter last year.

Help, please. I would read the help, but I don't know the question.

Ed,

Why do you have three columns?

It seems to me that you want the Quarter and year in one column and the appreciation in the second, sort by year and chart.