I have two worksheets. One has raw data with fields in columns and survey respondents in rows. This worksheet has Autofilters. In the second worksheet, I have some calculations based on the raw data. I use SUBTOTAL and IF statements so that calculations are based on filtered data that meet a criteria. The formulas have worked fine, but now I have added new data in the raw worksheet and some of the formulas do not work. I get #VALUE errors. Below are the formulas that “go bad”:

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0))),raw!$AB$2:$AR$64999))}

{=AVERAGE(IF((SUBTOTAL(3,OFFSET(raw!$U$2:$U$64999,ROW(raw!$U$2:$U$64999)-MIN(ROW(raw!$U$2:$U$64999)),0,1)))*(ISNUMBER(MATCH(raw!$U$2:$U$64999,{2,3,4,5},0)))*(raw!$AT$2:$BJ$64999>0),raw!$AT$2:$BJ$64999))}

These formulas basically calculate the average for values in ranges AB:AR and AT:BJ respectively, for data that is filtered that meet the criteria based on values in column U.

So when the original raw data is there, the formulas work fine. But when I add data by pasting it from another Excel file, these formulas stop functioning properly. I have checked that I have all the fields lined up correctly and that the field type is consistent. What could be going on here? What can I do to stop this from happening? Thanks in advance for your help.

Frank