Hi!
I'm using the following array formula to find the MEDIAN of a string of values:
{=INDEX($Q$4:$Q$1000,MATCH(MIN(ABS($Q$4:$Q$1000-MEDIAN($Q$4:$Q$1000))),ABS($Q$4:$Q$1000-MEDIAN($Q$4:$Q$1000)),0))}
Row Q is populated with the formula, =IF(ISNUMBER(M4),FV(M4,10,,-100000),""), =IF(ISNUMBER(M5),FV(M5,10,,-100000),""), etc.
I'm using the MEDIAN array formula with several different data sets. The formula works fine if the data set is complete in row Q, meaning that data is populated all of the way to row 1000. But if the data set ends before row 1000 and the formula in row Q returns "", the MEDIAN array formula returns #VALUE!.
Any help to fix this formula would be much appreciated. Thanks!!!
I'm using the following array formula to find the MEDIAN of a string of values:
{=INDEX($Q$4:$Q$1000,MATCH(MIN(ABS($Q$4:$Q$1000-MEDIAN($Q$4:$Q$1000))),ABS($Q$4:$Q$1000-MEDIAN($Q$4:$Q$1000)),0))}
Row Q is populated with the formula, =IF(ISNUMBER(M4),FV(M4,10,,-100000),""), =IF(ISNUMBER(M5),FV(M5,10,,-100000),""), etc.
I'm using the MEDIAN array formula with several different data sets. The formula works fine if the data set is complete in row Q, meaning that data is populated all of the way to row 1000. But if the data set ends before row 1000 and the formula in row Q returns "", the MEDIAN array formula returns #VALUE!.
Any help to fix this formula would be much appreciated. Thanks!!!