Median Formula with Object & Right Formula

[Former Member]

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?

[Former Member · Accepted solution]

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

[amitrathi239]

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

[Former Member]

Thank you. I did try this but it returns my value as 2,017. Because I'm adding text behind the formula, I cannot change the formatting of the number. Any other suggestions?

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

Comes out as: 2,017 Completed Months YTD. I want the comma to go away.

[amitrathi239]

use this then.

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