Median value

[JEP]

In a population of SAT scores, how does one go about determining the median value? Simple table structure, no links, field containing Verbal, Math and Percentiles. One column for each.

Thankyou

[Keith Hubert]

Re: Median value

Hi Jon,

Welcome to Alpha.

You will need a UDF for this. I cant see one built in.

Ten people are riding on a bus in Redmond, Washington. The mean (average) income of those riders is $50,000 a year. The median income of those riders is also $50,000 a year.

Joe Blow gets off the bus. Bill Gates gets on.

The median income of those riders remains $50,000 a year. But the mean income is now somewhere in the neighborhood of $50 million or so. A source now could say that the average income of those bus riders is 50 million bucks. But those other nine riders didn't become millionaires just because Bill Gates got on their bus. A reporter who writes that the "average rider" on that bus earns $50,000 a year, using the median, provides a far more accurate picture of those bus riders' place in the economy.

Check out http://msgboard.alphasoftware.com/al...ad.php?t=32736

[JEP]

Re: Median value

Thank you for the reply, I agree, no built ins! A thought provoking riddle!

Jon

[Tom Cone Jr]

Re: Median value

Here's a "target" folks can shoot at.

Brute force approach to finding median:
1) sort the records from lowest to highest
2) determine how many records are in the list
3) if the record count is odd, determine position of median value by dividing record count by 2, and then adding one; then fetch from lowest to highest stopping when you get to the position of the median value. This is the median value when the record count is odd.
4) if the record count is even, determine position of the last value that's lower than the median by dividing the record count by 2; determine position of the first value that's higher than the median by dividing the record count by 2 and adding 1; fetch through the records from lowest to highest, retrieving the last value that's lower than the median and the first value that's higher than the median. Then average those two values to get the mid-point between them. This is the median when the record count is even.

How'd I do?

[JEP]

Re: Median value

Excellent and extremely clear definition, however, how do I write this as an Alpha expression?

Thanks,

Jon

[Tom Cone Jr]

Re: Median value

Jon, can't be done in an expression.

You're going to have to write a script using xbasic. As others have recommended this could be saved in your database as a User Defined Function (UDF).

If you post some dummy data here I daresay that someone will write it for you.

-- tom

[JEP]

Re: Median value

Good advice, I will attempt do so and if I don't have any luck, I will post some data, thank you.

Jon

[Tony Rutherford]

Re: Median value

Jon,

I think this will work (I have only tested it on a few records, but I think the logic is right):

FUNCTION Median as N (vcTableName as C, vcField as C, vcIndex as C)

	'Calculates median value for specified table, field and index
	'� R A Rutherford 2008

	'Declare & initialise variables
	OPTION strict
	DIM vnErr as N							'Error number for error handling routines
	DIM vcErrMsg as C						'Error message for error handling routines
	DIM vnResp as N							'User response to message box
	DIM t as P								'Pointer to selected table
	DIM vnRecCount as N						'Number of records
	DIM vnRecPos1 as N						'Position of 1st median record in index order
	DIM vnRecPos2 as N						'Position of 2nd median record in index order
	DIM vlSingleRecord as L					'Odd number of records? (therefore don't need to average values for two records)
	DIM vnValue1 as N						'Value of 1st median record in index order						
	DIM vnValue2 as N						'Value of 2nd median record in index order		
	DIM i as N								'Counter

	'Open tables
	ON ERROR goto OpenError
	t = table.open(a5.Get_Path() + chr(92) + vcTableName + ".dbf", FILE_RO_SHARED)
	ON ERROR goto GeneralError
	t.index_primary_put(vcIndex)

	'Get number of records
	vnRecCount = t.records_get()
	vnRecPos1 = vnRecCount / 2
	IF vnRecPos1 <> int(vnRecPos1) THEN		'Odd number of records, so only need to look at one record
		vlSingleRecord = .T.
		vnRecPos1 = round_up(vnRecPos1, 0)
	ELSE
		vlSingleRecord = .F.
		vnRecPos1 = int(vnRecPos1)
		vnRecPos2 = vnRecPos1 + 1
	END IF

	'Fetch through records
	t.fetch_first()
	FOR i = 1 to vnRecPos1 - 1
		t.fetch_next()
	NEXT
	vnValue1 = eval("t." + vcField)
	IF vlSingleRecord = .F. THEN
		t.fetch_next()
		vnValue2 = eval("t." + vcField)
		Median = (vnValue1 + vnValue2) / 2
	ELSE
		Median = vnValue1
	END IF

	'Close table

	CloseAll:
	IF is_object(t) THEN
		t.close()
	END IF

	EXIT FUNCTION

	'***********************************************************
	'//Error handling routines

	'Errors not otherwise specified
	GeneralError:
	vnErr = error_code_get()
	vcErrMsg = error_text_get(vnErr)
	msgbox("Error", vcErrMsg, UI_STOP_SYMBOL + UI_OK)
	Median = 0					'possibly change this to an unlikely value e.g. -999999999?
	GOTO CloseAll

	'Error opening table
	OpenError:
	vnResp = msgbox("Error opening table", "Cannot open the table for median calculation." + crlf() + "Another user may be using it.", UI_STOP_SYMBOL + UI_RETRY_CANCEL)
	IF vnResp = ui_retry_selected THEN
		RESUME 0
	ELSE
		Median = 0
		GOTO CloseAll
	END IF

END FUNCTION

Tony

[JEP]

Re: Median value

Thank you!

It works fine, how can I thank you enough? Many thanks to all the others who took a shot at it.

Jon