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

10|10000 characters needed characters exceeded

Feb 14, 2018 at 04:38 PM

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

10|10000 characters needed characters exceeded
• Feb 14, 2018 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)))

10|10000 characters needed characters exceeded
• AMIT KUMAR Hillary Martin

use this then.

=formatnumber(Median(ToNumber(Right([Fiscal year];4)));"##") + "Completed Months YTD"