Simple Median Calculation

[CatLady001]

I know how to find the median of numbers in Excel, but I don't know how to do it in FMP.  I use FMP 16 and I can't find a "median" command.  I have three simple numbers that require a median, and I just need to know which command to use.  I know this is probably very simple, but my initial run through the forums for an answer didn't produce anything simple.  I can't do the calculation in Excel before importing into my database, because the source documents are different.  I can bring them together in FM as a calculated field, which would be the median.

Your help is appreciated! 😊

[comment]

There is no Median function or "command" in Filemaker. You need to make your own using a script, custom function or just a calculation. Which method is best depends on where your data is coming from: if it's always from 3 fields in the same record (and none of these fields will be ever empty), then you can calculate the median quite simply as =

Let ( [
v = List ( FieldA ; FieldB ; FieldC ) ;
s = SortValues ( v ; 2  )
] ;
GetValue ( s ; 2 )
)

--
P.S. Please use the default font when posting.

 

Edited May 6, 2020 by comment

[CatLady001]

Thank you...sorry about the font.  Didn't realize that even though I have a choice, I'm not supposed to use it.  😣

I'm a true novice at this, I'm afraid.  I'm trying to create the script, but I can't find the Let command in the lists.  Sorry for being so lame...

Also, there may be empty fields.

Edited May 6, 2020 by CatLady001
Added text

[comment]

It's not a script, it's a calculation. And if some of the fields can be empty, it needs to be a little more elaborate =

Let ( [
v = List ( FieldA ; FieldB ; FieldC ) ;
s = SortValues ( v ; 2 ) 
] ;
Choose ( 
ValueCount ( v ) - 1 ;
GetValue ( v ; 1 ) ;
Average ( FieldA ; FieldB ; FieldC ) ; 
GetValue ( s ; 2 ) 
)
)

See if the attached demo file helps.

Median.fmp12

[LaRetta]

Welcome to FMForums, CatLady!  There is nothing wrong with being a novice - we ALL were at one time or another and still are on many subjects.  This is the best forum for help and you're getting assistance from one of the best.  😃

[CatLady001]

LaRetta, thank you for your reassurance.  I appreciate it very much! ☺️

Thank you, Comment, for straightening me out.  I was able to create the calculation and it works fine.  I appreciate you both!  This is an awesome forum!  

Have a great day!

Edited May 6, 2020 by CatLady001
Added text

[Greg58]

Hi.  Hope it is ok to jump in with a question.  Just relating to the first method.  Is it right that V is the list of numbers, S sorts the list (the 2 means 'numbers'?

I can't get this to work on a test I am doing.  I am looking at the definition of 'GetValue' and it says it returns TEXT??

[comment]

Yes, v stands for list of values and s stands for sorted values. 

True, the result of GetValue() is text. And so is the result of List() and SortValues() (regardless of the datatype value). However, if you set the result type of the calculation field to Number (as you are supposed to do), then the returned value will be converted back to the original number.

 

Edited May 8, 2020 by comment