Hi all

I feel like this is going to turn out to be something completely stupid and obvious so apologies in advance but:

I have a formula to calculate the median value of an array, based on a number in a reference column. Basically I need to calculate the median for all of the cells with a reference number less than 7, and then all of those with a number greater than (equal to 7 is not used). The formula for the 'greater than' range works fine, but that for the 'less than' returns an N/A error, despite the two forumlae being identical in every way except for the < > symbol.

Here is the formula in question:

WORKS:

{=MEDIAN(IF('Complaints Reconciliation'!$A$2:$A$1006>7,IF('Complaints Reconciliation'!$M$2:$M$1000<>0,'Complaints Reconciliation'!$M$2:$M$1000,""),""))}

DOESN'T WORK:

{=MEDIAN(IF('Complaints Reconciliation'!$A$2:$A$1006<7,IF('Complaints Reconciliation'!$M$2:$M$1000<>0,'Complaints Reconciliation'!$M$2:$M$1000,""),""))}

Column A:A contains numbers from 1-6 and 8 - 11, column M:M contains numbers from 1 to about 400.

Any ideas?