Jump to content

Recommended Posts

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! 😊

Link to post
Share on other sites
Posted (edited)

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 by comment
  • Like 1
Link to post
Share on other sites
Posted (edited)

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 by CatLady001
Added text
Link to post
Share on other sites

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

  • Like 1
Link to post
Share on other sites

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.  😃

  • Like 1
Link to post
Share on other sites
Posted (edited)

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 by CatLady001
Added text
Link to post
Share on other sites

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??

Link to post
Share on other sites
Posted (edited)

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 by comment
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.