Averageifs formula with criteria as formulas

I am using the averageifs formula and I have two criteria to meet (state and year). Everything seems to work fine when I type my criteria in as a value, but when I put it in as a cell reference the formula doesn't work. For example, the following formula works fine:

=AVERAGEIFS($R$9:$R$2387,$N$9:$N$2387,"="&$BZ13,$M$9:$M$2387,">=1951",$M$9:$M$2387,"<=2011")*100 -- "$BZ13" refers to a state reference. However, when I replace the "<=2011" with "<="&$CB$3 (i.e., a cell reference for year) the formula does not work. Any idea what I'm doing wrong? Thanks.

Not sure what you mean by that as I don't have any formula within the AVERAGEIFS formula starting with "=". I do use the "=" and "<=" and ">=" symbols for my criteria. What I am trying to do is take the average of an array (R9:R2387) if the value is from a particular state (N29:N2387 = value in BZ13) and meeting a year requirement (M9:M2387 less than or equal to 2011 and greater than or equal to 1951 -- it is the 2011 and 1951 that I want to make into a cell reference). That is, I'm wanting the average value that meets three criteria (state=value in BZ13, year<=2011, and year>=1951).

=$BZ13 does not work in the AVERAGEIFS formula to the best of my knowledge. What I'm confused about is why the "="&$BZ13 works, but I can't use the same approach for the ">" or "<" operators.

I have checked both the values in my column of data with years and verified that all the data are values (1951-2012) and the value I had in my criteria cell (CB3) was also a value. Bottom line, I can't figure out what is wrong as I've checked data and formulas several times and everything seems fine.

I get an error (i.e., #DIV/0!). When I use =YEAR(CB3), where CB3=2011, I get 1905, which is a value that is not in my array (column of year values). However, I actually am never using the "YEAR" command anywhere. That is, what I have in column M (see initial formula) are values of 1951-2011 and they represent years, but I'm never technically telling Excel these are years (i.e., they are simply values).

The file I've uploaded (hopefully it has been uploaded properly) shows the problem I'm having. Column H has the correct answer, but you can see that I typed "1951" and "2011" into the formula. Column I has the formula I would like to use, which seems I should be able to, but something is obviously wrong. Thanks.

Nice catch! I forgot that I turned that on. Of course this is going to cause more problems for me because I'm in the process of converting an old Lotus file to Excel (showing my age a little there). When I "untick" this, I have other formulas in the spreadsheet that will blow-up, but I can redo them by changing the syntax. Thanks for the help!