## Forum Discussion

This is a formula for a numeric field that will evaluate the median value of another numeric field in the same table. It only accounts for non-zero values.

If you want to generate the median of a numeric field on a different table, you'll have to refactor the $arrayAsText formula query, but that shouldn't be difficult if you already have your query working.

I'm not a huge fan of formula queries because of their performance, so I can't promise this will work on a massive table with hundreds or thousands of unique values.

I tested it using less than 20 records and it returned the expected results. Depending on how many records you have, you should test it out to ensure it's working as expected.

`var text arrayAsText = SearchAndReplace(ToText(GetFieldValues(GetRecords("{3.GT.0}"), 16)), " ", "");`

` `

`var textlist array = Split($arrayAsText);`

` `

`var number arrayLength = Size($array);`

` `

`var number modulo = Mod($arrayLength, 2);`

` `

`var number median = If ( $modulo != 0,`

` `

` ToNumber(Part($arrayAsText, Ceil($arrayLength / 2), ";")),`

` `

` (ToNumber(Part($arrayAsText, $arrayLength / 2, ";")) + ToNumber(Part($arrayAsText, ($arrayLength / 2) + 1, ";"))) / 2`

` `

` );`

` `

`$median`

------------------------------

gary

------------------------------

- MelissaFreel12 months agoQrew Member
Gary:

Thanks, works like a charm and I don't forsee super large data sets as I have "filtered" appropriately.

------------------------------

Melissa Freel

------------------------------ - rvalenz23 days agoQrew Member
Hi,

Thank you for this query solution. i hope one day it becomes a native QB features.

I followed the format, and works most of the time. when it doesn't work, it seems to use a number higher or lower than the median, which makes me believe, that the numbers aren't sorting in order.

how can the list of values be correctly ordered?

Thanks.