INDIRECT function did the trick.
{=AVERAGE(IF((INDIRECT($R13)<>"")*(MOD(COLUMN(INDIRECT($R13)),F$3)=F$2),INDIRECT($R13)))}

Where R13 combined the first half of the range name with the second half which represented the date. Manually entering the range names, but way better than it was.