+ Reply to Thread
Results 1 to 9 of 9

Copied data and array formula returns #DIV/0!

  1. #1
    Registered User
    Join Date
    05-31-2014
    Posts
    4

    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. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-31-2014
    Posts
    4

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

    Sample of data.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #6
    Registered User
    Join Date
    05-31-2014
    Posts
    4

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

    Quote Originally Posted by FDibbins View Post
    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. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #8
    Registered User
    Join Date
    05-31-2014
    Posts
    4

    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. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data to be copied from Worksheet into Array gives error 1004
    By Inventiveman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 09:00 PM
  2. [SOLVED] increment all cell references in an array formula by 24 when copied down a row
    By ariapro22 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-29-2013, 05:51 AM
  3. Array formula returns a 0, but I don't want it to
    By keys in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-08-2008, 07:29 PM
  4. Combo Box returns from array formula
    By robcosta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-22-2008, 03:33 PM
  5. Array formula returns 0
    By erict in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2007, 02:23 PM

Bookmarks

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