# Copied data and array formula returns #DIV/0!

1. ## Copied data and array formula returns #DIV/0!

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.

2. ## Re: Copied data and array formula returns #DIV/0!

Hi and welcome to the forum

Suggest you upload a sample of what you are working with, so members can play with it?

3. ## Re: Copied data and array formula returns #DIV/0!

Sample of data.

4. ## Re: Copied data and array formula returns #DIV/0!

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.
Perhaps not everything? D2:E17 on DATA sheet are text

5. ## Re: Copied data and array formula returns #DIV/0!

If you are unavle to ensure the data is all numeric, adjust your formula to this...
=(AVERAGE(IF((DATA!\$A\$2:\$A\$40309=\$A5)*(DATA!\$E\$2:\$E\$40309*1=T\$4),DATA!\$F\$2:\$O\$40309)))

6. ## Re: Copied data and array formula returns #DIV/0!

Originally Posted by FDibbins
Perhaps not everything? D2:E17 on DATA sheet are text
Thanks for looking at this. The data I want to average are all numbers, no? I realize that some of the other data is not. Does it need to be? The formulas for previous years seem to handle it correctly. I wonder why there is a difference.

7. ## Re: Copied data and array formula returns #DIV/0!

It is looking for numeric 2013, but you have text 2013, thats why it cannot find it. you are getting the error not because it is trying to average non-numeric, but because it cannot find a match for 1 of the criteria. If your years in SSC sheet were text, then 2010-2012 would error instead (if that makes sense?)

8. ## Re: Copied data and array formula returns #DIV/0!

The *1 term solved the problem. I had tried various combinations of making sure the years were all numbers or all text, but it seems like just changing the cell format is not enough. For instance, I formated all cells for years in both worksheets as numbers and did not add the *1 terms to the formula, and it would not calculate correctly. Check with ISNUMBER and they are all numbers. But then I multiply each value in the the table by 1 and copy the resulting values back to the E column, run the formula without the *1 and it works. So I guess what I didn't understand is that Excel does not seem to always consider a number to be a number even when the cell is formated as number and ISNUMBER = true.

Anyway, problem solved. Thank you very much.

9. ## Re: Copied data and array formula returns #DIV/0!

Formatting does not change the underlying content of a cell, just the cosmetics. So just because you change the format of a text entry to numeric, does not make it numeric - you have to physically change the contents.

Changing text 2013 to numeric format is exactly the same as changing text word to numeric format

the *1 (or you could use +0) forces the cell contents to change - or in this case, changes it while it is being used.

Glad we got this resolved for you, and thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1