My array formula looks like this:
{=(AVERAGE(IF((DATA!$A$2:$A$65534=$A5)*(DATA!$E$2:$E$65534=R$4),DATA!$F$2:$O$65534)))}
I have an existing spreadsheet with data and array formulas that work just fine on numeric data from 2010, 2011, and 2012. I want to add new data from 2013. The source of the new data was a CSV file that I saved as an XLSX file. I copied and pasted data to append the 2013 data to the original worksheet with the three previous years of data. I have made sure that all the columns are correctly in register and done everything I know how to do to make sure the numeric data are really numbers including testing with ISNUMBER.
I have columns in a second summary worksheet within the workbook that are labeled 2010, 2011, 2012. I added a fourth column named 2013 (that's the R$4 reference) and then copied the array formula from the adjacent cell under 2012 into the new 2013 column. The $A5 reference is the unique value for the variables in column A. The formula is supposed to average all numeric values in the data table where the value in columns A and E match the criteria $A5 and R$4 in the summary table.
Okay, so when I do that, and make sure I use SHIFT-CTRL-ENTER to verify that the formula is entered as an array formula, the calculation returns #DIV/0!. I also started from scratch and tried to create a AVERAGEIFS formula to do the same thing and I get a #VALUE error.
Just for grins, I copied the formula for the 2012 column to the 2010 and 2011 columns, and the results are as expected. I suspect there is something wrong with the data I copied over from what was originally a CSV file saved as an XLSX, but from everything I am able to check, including checking for extra spaces, it is in the same format and looks the same as the data from the previous three years.
Bookmarks