• Welcome to AppraisersForum.com, the premier online  community for the discussion of real estate appraisal. Register a free account to be able to post and unlock additional forums and features.

Excel - Spreadsheets

Status
Not open for further replies.

c w d

Thread Starter
Senior Member
Joined
Oct 2, 2006
Professional Status
General Public
State
Florida
Is anyone savvy with Excel? I'm trying to figure out how to calculate median, mean and mode for a range of sales between specific dates. However, I'm not that advanced with Excel and I have not been able to find the answer after a couple of hours of research on the net. All I seem to find is how to calculate the numbers of days, hours, minutes and seconds between two dates using date arithmetic.

What I'm doing is importing sales data from our MLS into Excel and trying to get excel to find, for example, all sales in January and run statistical calculations on their sales price.
 

Lawrence R.

Senior Member
Joined
Mar 27, 2007
Professional Status
Certified General Appraiser
State
South Carolina
Think this would do it

Type =MEAN in a cell then an open parenthesis. Highlight the group of numbers you wnat to perform the application to, and then close the parenthesis. Should work for mean and mode same same.
 

c w d

Thread Starter
Senior Member
Joined
Oct 2, 2006
Professional Status
General Public
State
Florida
Thanks for you reply Larry. But, I'm trying to automate the process by having Excel recognize the values between certain dates in order to determine the mean, median and mode. I understand how to calculate those in Excel. I just don't want to have to select the data ranges each and every time I want to perform the analysis. I want excel to recognize the data ranges on its own.

If I can find this one solution then everything I want to do will be doable with a minimum of interaction with future worksheets.
 
Last edited:

nauthead

Senior Member
Joined
Nov 26, 2004
Professional Status
Certified General Appraiser
State
Florida
C W D, what MLS system do you use? I export lots of info the spreadsheets-might be able to help.
 

Mike Plumlee

Junior Member
Joined
Feb 21, 2007
Professional Status
Certified Residential Appraiser
State
Texas
There's about 4 different ways to do what you want. The difficulty is the automation part based on when the month portion of the date field changes.

It will end up being a very long IF-THEN formula, but it can be done.

I suggest going to either:
http://www.ozgrid.com/forum/
or
http://www.mrexcel.com

Both of those are forum resources frequented by excel wizards and vba geeks. You can search the forum or post your own problem and someone will create the answer.
 

Artemis Fowl

Senior Member
Joined
Mar 16, 2004
Professional Status
Certified Residential Appraiser
State
Michigan
You will need 1 formula for each type of information you need (median, minimum, maximum, total volume, etc) for each time period you use. Then, add a flag column to your raw data. Meaning...create a formula in an adjacent cell to display the month, quarter, whatever. You can now reference the flag column in your if-then formula to extract only the relevant data. Many formulas will need to be "array" formulas. Absolute referencing is a big time saver when applied correctly.

I've spent months developing my excel data analysis file. The info is out there...you just need to find it. Worth doing.

Here's a few formulas to get you started:

(Cell I8 is where the date is)
=TEXT($I8,"mmmyy") ---Converts date #'s to a string (ex: Jan08)

=YEAR(I8)&"Q"&CEILING(MONTH(I8)/12*4,1) ---Converts date #'s to a string for quarters (ex: 2008Q1)

Read up on sumif
Understand arrays {=median(if.....)}

Really understand if-then formulas...you can use them for just about anything!
 

David Wimpelberg

Moderator
Staff member
Moderator
Joined
Mar 30, 2005
Professional Status
Certified General Appraiser
State
New York
I use Excel for basic functions. I prefer Access for the type of queries that you are proposing.
 
Status
Not open for further replies.
Find a Real Estate Appraiser - Enter Zip Code

Copyright © 2000-, AppraisersForum.com, All Rights Reserved
AppraisersForum.com is proudly hosted by the folks at
AppraiserSites.com
Top

AdBlock Detected

We get it, advertisements are annoying!

Sure, ad-blocking software does a great job at blocking ads, but it also blocks useful features of our website. For the best site experience please disable your AdBlocker.

I've Disabled AdBlock
No Thanks