Excel - Spreadsheets

[c w d]

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.]

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]

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.

 

[nauthead]

C W D, what MLS system do you use? I export lots of info the spreadsheets-might be able to help.

 

[Mike Plumlee]

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]

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]

I use Excel for basic functions. I prefer Access for the type of queries that you are proposing.