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?
Bookmarks