median value

[Mary Haugan]

How do I create a median value instead of an average value of a number field in FMP 6?

 

Thanks for your help.

[Mary Haugan]

OK. I searched for some posts and found the script and conversation about medians, etc.

 

Problem is, I don't know if this will work if I have records with the same values. I mean what if my list of values is as follows:

 

1 value of 1

4 values of 2

6 values of 4

2 values of 10

 

Does the script take this into account? Just checking.

[Ender]

What's the script?? Whatever you find, you should always test it anyway.

[Mary Haugan]

I'd rather not do a script, but if that's the only way....

 

Anyway, the script is from Thread - Is there a "Median" function, or just "Average"

 

Sol, the following assumes that you are working with numbers and your field is named "Data".

 

Create 3 global number fields: "gFirst_Num" "gNext_Num" and "gMedian"

Create 2 calc fields:

"Record_No1" = Round(Status(CurrentFoundCount)/2,0)

"Record_No2" = Status(CurrentFoundCount)/2+1

 

Place the "gMedian" field on your layout, the other fields do not need to be on the layout.

Sort on field "Data" ascending.

 

Then create the following script:

 

median script

 

Sort [ Sort Order: Data (Ascending) ]

[ Restore sort order, No dialog ]

If [ Mod(Status( CurrentFoundCount),2)=0 ]

Go to Record/Request/Page [ Record_No1 ]

[ By Field Value... ]

Set Field [ gFirst_Num, Data ]

Go to Record/Request/Page [ Record_No2 ]

[ By Field Value... ]

Set Field [ gNext_Num, Data ]

Set Field [ gMedian, Average(gFirst_Num,gNext_Num) ]

Else

Go to Record/Request/Page [ Record_No1 ]

[ By Field Value... ]

Set Field [ gMedian, Data ]

End If

Exit Record/Request

[Ender]

I think in FM5/6, a script is the only way to do it. In FM7 Developer or FM8/8.5/9 Advanced, you could use a custom function.

 

I don't have time to thoroughly test that script, but I'm a little doubious about the round() function.

 

Why don't you try it and let us know.

[Mary Haugan]

well, it'll have to wait until next week, but I will keep you "posted". heh, heh