Array Using Median Function

Question

I'm a self taught YouTube Excel user and having trouble debugging sporadic #NUM! errors returned in a column.  I have a 3600 row worksheet with a few columns.  One column is state abbrev (Texas = TX) and two columns of dates (mm/dd/yyyy).  I used the Datedif function to come up with the number of days between the dates (0 to 85 number values returned) and added a new column to store the results.  The state abbrev is column A, and Datedif results in column D.  I created the following formula, {=MEDIAN(IF($A$2:$A$3600=A2,$D$2:$D$3600))} and copied (+) to populate a fifth column.

My issue is some cells in the fifth column are returning #NUM! errors, looks like when rows have TX or NC in state abbrev column.  I need to add other median columns for type, regions, and branches.  I don't want to proceed until the first problem is resolved.  I've tried the state abbrev format as both general and text as well as copying the Datedif column and pasting "values" into a new column with the same results.

Any advice or help would be greatly appreciated.

Answer 1

Hello GrasshopperSS

I am V. Arya, Independent Advisor, to work with you on this issue. Try using the below array formula

=MEDIAN(IF($A$2:$A$3600=A2,IF(ISNUMBER($D$2:$D$3600),$D$2:$D$3600)))

Answer 2

Thank you V. Arya!  The addition of the ISNUMBER did the trick.