AVERAGEIFS function won’t work with cell references

Question

I have values in a range of cells, C9 thru G9, that I am using for an AVERAGEIFS function.  I also have MAX & MIN values, J9 & K9, which cells are populated by the formulas =VALUE(MAX(C9:G9)) and =VALUE(MIN(C9:G9)).

The AVERAGEIFS function I tried to use is: =AVERAGEIFS($C9:$G9,$C9:$G9,"<J9",$C9:$G9,">K9"), but I get a # DIV/0! error message.  I can get the formula to work if I type the actual values of J9 & K9 into the formula, in this case =AVERAGEIFS($C9:$G9,$C9:$G9,"<32.83",$C9:$G9,">32.55"), but typing the values into each formula on each line is a pain.  HELP!

Answer 1

Instead of "<J9" and ">K9", use "<"&J9 and ">"&K9 respectively.

Answer 2

Thanks!  That worked perfectly!