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?
=formatnumber(Median(ToNumber(Right([Fiscal year];4)));"##")
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)))
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.
use this then.
=formatnumber(Median(ToNumber(Right([Fiscal year];4)));"##") + "Completed Months YTD"