Appraisers' Free Forum

This is a very basic discussion of spreadsheets for those who have no idea what they are or what they do.

A spreadsheet is a document which is composed of many, many cells: 65, 535 of them, in most cases. It's a document which is made up of a huge table; it has also been described as an electronic ledger sheet. The most commonly used spreadsheet is Microsoft Excel, but there are many others: Lotus, Quattro Pro, Mesa (Mac only), and OpenOffice Calc to name a few. To learn to use a spreadsheet, you'll need to acquire one. If you don't already have Microsoft Office (which contains Excel), the expense ($229.95 at Buy.com here: http://www.buy.com/prod/microsoft-office-2007-ultimate-complete-product-1-pc/q/loc/105/204071247.html) may drive you away, but the Office suite is by far the business leader. You'll find MS Office at prices ranging from the one shown here, all the way up to about $550. Shop around. It's the same program.

If you're somewhat computer savvy (doesn't take much, though), you can download OpenOffice for free, and use its Calc program. You can find it at http://download.openoffice.org/2.2.1/index.html, but you'll want a broadband connection; it's about a 110MB download. There are many companies which provide OpenOffice and its documentation on CD-ROM for a small fee ($10-20 or so), if you don't have a broadband connection. Find them here: http://distribution.openoffice.org/cdro ... html#cdrom

Organization of a spreadsheet:

You need to know about rows, columns, ranges, cell references, and cell formulas to understand what you're trying to do with a spreadsheet.

Rows and Columns: Spreadsheets are made up of rows, usually 1-65,535; and columns A - ZZZZ. You refer to a cell by its Column and Row designations. The top left cell is A1. Five cells right of that first cell is F1. Two rows down from that last cell is F3, and so on. This is called a cell reference.

Cell Formulas and Cell Ranges: You can tell the spreadsheet to add, subtract, multiply, divide, exponentiate, or do any of a host of other mathematical operations on the contents of cells. For example, if we have a column of numbers starting in Cell A1 and extending to A5, we can add them up and put the total in cell A6 by simply clicking in cell A6 and entering =SUM(A1:A5). When you hit Return, the sum of the first five cells appears in A6. Now click in A7 and enter =AVERAGE(A1:A5). Guess what happens in A7. Okay, we'll complicate things some more: Click in A8, and enter =STDEV(A1:A5). The standard deviation appears in A8. Try these: =MAX(A1:A8); MIN(A1:A8). Some spreadsheets like OpenOffice also have =MEDIAN(A1:A5); you'll just have to try that one to see whether Excel has it or not. The "A1:A5" part defines the range over which the function is to work. In other words, tell it which cells you want the function to work with. This is called a Cell Range.

Suppose we have a spreadsheet composed of data downloaded from the MLS. The columns are referenced by their column letter, but looking across the top of the sheet, we see that the columns are also named: Address, Sale Date, Sale Price, DOM, GLA, etc. The rows contain the individual sales we want to analyze; let's say that our search tuned up 340 sales in the past year. This sheet would have 341 rows and as many columns as are needed for each of the data items. Think of a row as a single record of a sale, and the columns within that row as the pieces of that sale which describe it.

But we said there were only 340 sales. How come we've got 341 rows, then? Because you've got a header row. That's the first row across the top of the page which describes the information in each of the columns: GLA, Price, Sale Date, etc. So there's one row describing the data, and 340 rows listing it.

If we want to find out the maximum and minimum sale prices, for example, we'd use the MAX and MIN functions, and tell them what range to look in. Let's say the Sale Price is located in column Y. We already know the last row of the table is row 341. So the cell formula used in cell Y342 would read =MAX(Y2:Y341), and the highest sale price in that range would be shown in Cell Y342. (Remember, Y1 is the header for Column Y, and doesn't contain a number. It contains only the words, "Sale Price". So we tell the function to work from Y2 to Y341).

Now that you know a little of the jargon found in discussing spreadsheets, try the tutorial located here: http://www.usd.edu/trio/tut/excel/index.html. This is a web page on the University of South Dakota website. To learn more about Excel, try Microsoft's own website: http://office.microsoft.com/training/training.aspx?AssetID=RC100620751033&pid=CR100479681033 Additionally, you can try the MrExcel website at http://www.mrexcel.com/for tutorials about how to do specific things using Excel. This is a very good site, and has video tutorials available for purchase. It also has two-minute podcasts to which you can subscribe, published each business day. But get familiar with spreadsheets first; use the USD site to learn the terminology and basics better. Then try MrExcel.

Most of the things you learn about Excel can be adapted to work in OpenOffice Calc or other spreadsheets. But there are slight differences. In Excel, the PMT function needs three other cells to work with: It needs to know the rate of interest per year, the term of the note in years, and the principal amount. Put those in cells A1, B1, and C1 (down the first column). In D1, enter =PMT(A1/12, B1*12, -C1). The payment will magically appear when you hit "return." In OpenOffice Calc, you'd enter =PMT(A1/12; B1*12; -C1). The only difference is that the arguments are separated by semicolons in Calc and by commas in Excel.

That's a little more computer jargon to learn: What you put in the parentheses of a function is called an argument. Plural, if there's more than one of them. They're also called "Function parameters."

Later on, we'll be publishing some tutorials about how to import MLS data into your spreadsheet; how to add more columns to facilitate analysis; how to chart your results; and how to use spreadsheets for market analysis.

Post any questions here, and one of us will answer them.

As humans, we use certain symbols for arithmetic: + - x and ÷ for addition, subtraction, multiplication and division. Computers, of course, do it differently. In a spreadsheet, (and, FYI, in a computer program) addition and subtraction are represented the usual way. But instead of division being represented by ÷, you use / and instead of using the "x" for multiplication, you use * . I'm keeping the punctuation around these at a distance. You use only the single character to invoke the operation.

So 3 divided by 4 would be 3/4. Not too weird. But for 3 times 4, you'd use 3*4, which takes some getting-used-to.

Powers of numbers are taken by using the caret, or shifted 6, like this 3^4 would be 3 to the fourth power. And to take the root of a number, use a fraction. E.g., the fourth root of 2756 would be 2756^(1/4). Note the parentheses. They're extremely important.

Parentheses tell the spreadsheet, "Solve this part first, before you do the rest of the problem." You can nest them as deeply as you can stand it, and that causes problems for humans trying to read this stuff. In the little problem above, we told the spreadsheet to divide one by four, and then to raise 2756 to the power of whatever you get for the answer. In this case, 0.25 results in the fourth root of the first number being returned.

Try a simple one in your spreadsheet: Click in any empty cell and type =49^(1/2). You should see 7 in that cell after you hit return. You told it to take the second root of 49, AKA the square root. (Note: Spreadsheets have a separate square root function defined. To use that, type =SQRT(49). The fractional root notation is shorter, but may not be as easy for some to remember.)

But those nested parentheses can get pretty bad. Look at this formula for the present value of an annuity due:
=(Pmt*((1-(1/((1+i)^(n-1))))/i))+Pmt
Pretty grim, huh?
Here's what the algebraic expression looks like:

Not much better, but a little easier to understand.

In the next post, I'll show you how to construct these complex algebraic formulas. We don't need many of them for appraisal work, but learning this will help you track down errors in your spreadsheet logic. You'll also find out how I was able to use terms like "Pmt" in that formula above.

I look forward to it.

Thank you Jim. I've been playing for hours. Finally figured out how to download some data from the MLS. (the most difficult part)

Now I read your other post .... my head hurts. Can the math get any more difficult? I have wondered many times why I passed algebra ... don't have a clue but I did. (divine intervention?) I think I have a mental block.

THANK YOU VERY MUCH JIM. I think you should publish the final product. 80% of my computer knowledge comes from our sons teaching me. The trial & error experience has been excruciatingly painful (still is) I can't tell you how many attempts I've made creating a spread sheet.

Can't help but get it right this time with your easy to follow instructions.


Thanks Again,

Okay, as promised, here it is.
Dang thing was too long and contained too many pictures to display as a regular forum post, so you'll have to download the attached PDF.

Write if you have questions.

My head hurts...thanks, Jim. That is a lot of good information!!!

Jim Plante wrote:Okay, as promised, here it is.
Dang thing was too long and contained too many pictures to display as a regular forum post, so you'll have to download the attached PDF.

Write if you have questions.

Nevermind an Ooooops.

Thanks Again

benluby wrote:My head hurts...thanks, Jim. That is a lot of good information!!!

I already said that !!!

Thank you Einstein ..... ummm, I mean Jim. It may take several days to work one formula. I'll be back for more brain cell torture.

<hugs>

Here's one you can try on your own:


It's the Future Value of an Annuity Due. Use the same sheet you started for the last one. Just follow the steps outlined in the PDF I attached above, and you should do OK. Add 1+i, take it to the power of n, and subtract 1 from it; Divide that by i. Multiply the result by Pmt. Then, multiply by 1+i.

The Excel function is FV(). Look it up in the Excel Help to find the proper usage. The formula is:
=FV(i,n,Pmt,,1) for Annuity Due. For plain-old Future Value of a lump-sum like you do for discounting, use this:
=FV(i,n,Pmt), leaving off the last two terms.

Two terms? Yep. The full formula, as outlined in the Help file, shows =FV(rate,number of periods,Payment, Present Value, Type). We don't "know" the present value, usually, so leave it blank. Your formula comes out to be:
=FV(i,n,Pmt,(nothing), 0 or 1). You have to reserve space for the (nothing) if you're going to specify the type. Using zero tells the function to use a standard lump-sum annuity. Since that's the "default" behavior, you can leave off the last TWO parameters. But if you want to use the Annuity Due formula, you'll have to save a seat for the (nothing) parameter, so the final formula has two commas in a row: =FV(i,n,Pmt,,1)

Well, to not let this die - I was surfing and found an article that should be helpful to some: Graphing market trends

http://appraisalnewsonline.typepad.com/ ... .html#more

That's a great article, Otis. Shows you what a good technical writer can do (as opposed to yours truly.)

There are many different MLS systems, and not all of them work alike. Each has different options. In the end, though, the .csv or .txt file you export from MLS should be easy to open in Excel or OpenOffice.

Most of the time it just works. But there are a few things to watch out for: Dates are not always dates, and dollars are not always dollars. You have to remember to tell the spreadsheet what kind of values to expect in those columns, especially if the columns contain "$" and commas. It can usually sort out dates, because they're usually exported in MM/DD/YY format (e.g. "10/01/07"). But some databases like to spit them out in YYYYMMDD format (e.g. 20071001), so you'll have to tell the sheet about it if it has trouble sorting that out.

Thanks Jim - here's another link to some other excel stuff: http://appraisalnewsonline.typepad.com/ ... t-of-.html

Boy, this is a good illustration of what not to do:

Part 2 of the introduction to Regression Analysis Add-on Module. We're ready to run the application which will generate a regression model which will be applied to the sales and subject property. The application will create a chart with actual sales prices and predicted values. The appraiser can then remove outliers (bad data - large value differences) to improve the results.

Bold mine.

You don't remove data points just because they fail to support your model. Data is data; sales are sales. You might remove "large value differences" because the sale wasn't arm's length; because the sale included significant personal property; or for many other reasons which might be inconsistent with the definition of market value. But you don't remove them just because they skew your regression line.

Do the scatterplot first. Look for outliers (points that are widely separated from the rest of the data cloud.) Investigate them. If you can't find an affirmative reason to eliminate them from the data set, you have to leave them in, regardless of what it does to your regression.

With that little bug in the instructions having been dealt with, the link offers a very cool tool. Just be sure you use it correctly.

Here's another statistical "gotcha:"

You'll see that the appraiser can then delete columns of data an re-run the analysis if he finds any adjustments that do not appear reasonable and adjust the sales if necessary.

From my personal experience, this will force the adjustments into another area which then proxies for the original characteristic.

What he's describing is called "stepwise regression." In using it, you can either go from bottom up (as illustrated here) eliminating columns (independent variables); or you can go from top down, beginning with one variable and adding more until you hit "optimal complexity," i.e., until you have enough independent variables to explain most of the price difference. Look up "stepwise regression" in the literature, and you'll find a number of reasons why this procedure isn't very well favored among mathematicians.

Again, the tool is useful, but KNOW WHAT YOU'RE DOING!

Here are some comments on stepwise regression, most of which are beyond my ability to explain well, and none of which are complimentary:http://www.stata.com/support/faqs/stat/stepwise.html