Median and Mode

[DanBrill]

This is an offshoot of another thread....

Average() gives you a Mean average. There is no function for Median or Mode averages. I've sorta thought of ways -- though I haven't gotten to execution yet -- to get each of these by setting up relationships, sorting them, counting them, using the mod() function, other calcs, etc. But this wouldn't be a clean as just typing Median(var).

So before I do need this...

Has anyone actually done this already?

Has anyone come up with a custom function for this?

Why wouldn't this be a standard function anyway?

Thanks,

Dan

[-Queue-]

Here's one way to determine the median if the field data is separate. It would be easier if a repeating field were used. I'm not sure how a custom function would do it.

Median.zip

[DanBrill]

Cool. I also see how let() works now. I also had never thought of using length() as a boolean evaluator.

Thanks,

Dan

[-Queue-]

I use Length when I'm too lazy to type not IsEmpty. But as long as you're only testing it like a boolean, any function that returns a zero or greater can be treated as a boolean, such as PatternCount, Position, etc. You know that if such a function returns zero, then nothing will happen; otherwise, regardless of how large a number it produces, it's 'true'.

[SteveB]

I think what you're doing is a bad idea. It might work ok in the current version (as does considering '0' and 'F' vs. '1' and 'T' the same), but it might not the next time FMI makes a change. Note the documentation on True and False states that what was permitted in v 6 and below won't work in v 7. If they make a change, this type of bug would be hard to spot.

Steve

[-Queue-]

I don't think there will be a problem. This is common behavior in most languages. It's the way computers interpret variables anyway, so why shouldn't the program continue to do the same? If you're paranoid about it though, it's easy enough to prepend "not not" to force it to be either 1 or zero.

[-Queue-]

Here's my crack at a Custom Function version.

MedianCF.zip

[Reed]

Here's my custom function for the median. This function accepts a return-delimited list as its lone parameter. This is handy because you can easily generate a calculation field that contains the contents of a sorted value list of related data using the ValueListItems() function.

Here's the code for those that don't have developer:

median (values)

/*

This function takes a return-delimited list

*/

Let (

[ count = ValueCount ( values ) ;

oddMiddle = Substitute (MiddleValues ( values ; Truncate(count/2;0) +1 ; 1 );"

median.fp7.zip

[Reed]

I just realized that this doesn't really work, since a value list only shows unique values.... Well, back to the drawing board... Can anyone else think of a way to do the median, mode without using a script?

[-Queue-]

The value list idea should work, if you define it to sort by a unique second field.

[RalphL]

Make a calculated field = Test Field & " " & Serial Number. Use this field for the value list. You may want to pad the Test Field with leading zeros.

[-Queue-]

Then you'll have to account for the serial number and pull only the valid data out. It's easier to use a secondary sort field, which won't be seen in a ValueListItems display.

[RalphL]

The user does have to see the value list and it is a simple matter to separate the two values using LeftWord function. I would think it would take more effort to generate the unique sort field.

[-Queue-]

How do you figure it would take more effort? Just set it to use the related serial. Done.

[RalphL]

OK, maybe I am a little dense today, but I don't know what you mean by the related serial number and how this would sort the data in the correct order.

[-Queue-]

Okay, I reread it. Now I see your point. I had assumed the related data referred to would be in order of creation. So sorting on the id would be a simple matter. You could still do a descending sort on a related calculation of Sum(rel::value) - value, where rel is a self-relationship based on unique parent id. But this would probably take longer to sort, depending on how many values were entered.

[-Queue-]

Of course, you could always use a Custom Function that resorts the values, and then base the calculations off that.

[Reed]

Thanks for the ideas on how to get a list into a field using valuelistitems. My workaround was a bit less efficient. I made a valuelist of the serials sorted by the values, returned the middle serial values into calc fields, used additional relationships to get the proper values to evaluate the median. These extra relationships led to refresh issues though.

There is a nice example of using merge sort to sort a list in a custom function on cleveland consulting's forum..... some people are smarter than me....

[Reed]

I ended up using Ralph's idea about a combination value & " " & serial field. Except I made the result of the calculation a number so that it would sort the same as the value itself.

The value list is defined as showing the value, but being sorted by the calc field. This way all of the values (even dupes) will be returned by the ValueListItems() function, and they will be sorted in the proper order for my median() function, so a separate sort function is not necassary.

[Reed]

Here is my modified file using the method I described in the last post....

median.fp7.zip

[Tripod]

Just wanted to say that I found this to be very cool--I do a lot of statistical work and have always wished there were built in functions for all measures of central tendency. I have seen people move to Access over this.

--Tripod

[Reed]

I think also that using one of the custom functions from cleveland consultings web site, one could write a mode() custom function. I think there is a function there that can count the number of occurrences of a word and return the index as a return-delimited list.

I'm going to give that a try next, after I finish updating all our prices and billing info, and finish my scheduling software, and maybe even do some things that are in a chemist's job description....