I have been trying to figure how to do this for several hour and don't know if it is possible. I am trying to run a query and want something like a median function but I know that access don't have that function. Is there a way to go around it. I don't want to use the average function.
Example: Field Name: Zip Code, Rent
Trying to find the median for Rent.
The median of these numbers would be: 1000. It's the middle number when they are listed in order by rent. The median is helpful for "ignoring" very high and very low numbers when doing an average. In this example, the average is 4700 (add them up and divide by 5). Because the 20000 is so high, the average is outside the range of the other 4, whereas the median is in there.
I was trying to do this recently and I couldn't figure it out, but I got around it by using iterative queries. I made a query that sorted my data for only those records whose value is within 10% of the average. Then I ran a query to find new averages based on these records only. Then I created a query based on that one that sorted my data for only those records whose value is within 10% of the new averages. After doing 5 iterations, the values stabilized somewhat and were "ignoring" the extreme highs and lows in my data.
The median number is not 1000, but 1500. you just can't order the values, because rents are payed by month, week, or day, it doesn't matter, what i mean is that we are dealing with a specific time, which belongs the record, so the second record is previous to the third and fourth, so the value 1000 can't be puted in third place. If dates and the order of statistical collected data, are no problem, then you are right, ordering the values, you have a median with 1000.
But i don't understand what do you mean with:
"The median is helpful for "ignoring" very high and very low numbers when doing an average. In this example, the average is 4700 (add them up and divide by 5). Because the 20000 is so high, the average is outside the range of the other 4, whereas the median is in there.
I was trying to do this recently and I couldn't figure it out, but I got around it by using iterative queries. I made a query that sorted my data for only those records whose value is within 10% of the average".
No. In first place, median just marks the center of a distribuiton, and not for very high and very low, ignoring purpposes.
Second, the median and average are totaly diferent. You can have an average of " -10500 " and a median of " 1 ".
So when you say you sorted your values within 10% of the average, you were doing a wrong calculation for the median. Don't mistake median with "Standard Deviation" which is given by:
SQR( SUM (i=1 to n) ((Valuei - Average)^2/(n - 1)) )
which in this case will be: 8339.51
This means that the deviation from the average is in 8339.51, due to the great value of 20000.
What i mean with this, is: You can not calculate a median only with values with 10% of the average, because median doesn't have nothing to do with average. Median is calculated by: (n+1)/2,
being n the size of the serie.
If you have:
-15; -30; 20; 45; 150; 20000; -800000
The median value is: (7+1)/2 = 4, the value which the position in the serie ranges 4, is: 45 ->( Median of the distribuition. Ranging 3 to the left and 3 to the right, centered by 45. If you had a non integer median value, then the distribuition had no median, the left and right range would be equal without a number to center.)
The average is: -111404.29
Now if you sorted the values within 10% from -111404.29, you wouldn't have a median, because no values would be returned.
See the point!?
To calculate a median, you should count the records in the table, use the Dcount function. for example:
Dim dbs as Database
Dim n as single
Dim median as single
' Or use the MoveFirst and MoveLast
If ( (n+1) Mod 2 ) <> 0 then
Msgbox "No median was find!" & VbCrlf & VbCrlf & "The result is a non integer number"
' After this use the property Move, so the records can move to the median record. Or just use the Dlookup function to search for the record wich the field "number of record" is the median.