Skip to Content

Median Formula with Object & Right Formula

Feb 14 at 03:57 PM


avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Hillary Martin Feb 14 at 04:38 PM

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

10 |10000 characters needed characters left 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)))

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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"