Skip to Content

Median Formula with Object & Right Formula

I'm trying to write a formula to determine the median year. For example, I have three years in my report: 2016, 2017, and 2018. I need to return 2017 as my value.

Trying to use the below formula:

=Median(Right([Fiscal year];4))

This formula works with the Min and Max formulas, but not the Median.

=Min(Right([Fiscal year];4)) works correctly.

=Median(Right([Fiscal year];4)) does not work. I get error "The expression or sub-expression at position 8 in the 'Median' function uses an invalid data type (IES 10037)"

What am I missing?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Feb 14 at 04:38 PM

    =formatnumber(Median(ToNumber(Right([Fiscal year];4)));"##")

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 14 at 04:18 PM

    The reason of Median function is not working as you are applying on the string datatype.

    First you have to convert string to number and then apply median().

    =Median(tonumber(Right([Fiscal year];4)))

    One more observation MIN/MAX functions will not retrun always correct values with string datatype.Convert then also in number data type.

    =Min(tonumber(Right([Fiscal year];4)))

    Add comment
    10|10000 characters needed characters exceeded