+ Reply to Thread
Results 1 to 7 of 7

Array formula returns a 0, but I don't want it to

  1. #1
    Registered User
    Join Date
    09-03-2008
    Location
    california
    Posts
    3

    Array formula returns a 0, but I don't want it to

    I have a simple array formula in "sheet2" that pulls data from "sheet1" in the range of -1 to 1. It looks like this:

    =SUM((Sheet2!$B10=Sheet1!$A$12:$A$13)*(Sheet2!C$9=Sheet1!$B$11:$G$11)*(Sheet1!$B$12:$G$13))

    Every month, more data is added, but I want to be able to drag the formula for the rest of the year even though the data for the next 4 months does not exist yet.

    The problem is my array formula returns empty cells as 0. I tried using an IF statement to put zeros as NA(), but some of my data includes zeros and I want those zeros to show up.

    Finally, the reason I want zeros to show up, but not empty cell zeros is because I have the array data automatically charted on a line graph and I don't want the line dropping to zero just because the data is nonexistent.

    Thanks in advance for any help!

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    I think you need to attach the spreadsheet, or explain a bit more. We do not know what any of the cell references refers to. I also wonder if a sumproduct would be a better way to go for the solution.

    Regards

    Dav

  3. #3
    Registered User
    Join Date
    09-03-2008
    Location
    california
    Posts
    3
    Attached is a basic example of what I'm trying to do.

    As you can see in cell E11 and F12 of the Rawdata sheet, there are zeros. But for the rest of the months in the year there are blank cells. I want the actual zeros to show up on the ArrayFormula sheet, but I want the blank cells to return #NA. This is because I have a chart that is automatically updating each month and I want it to show the zeros, but I don't want it to show zeros for months that don't exist yet.

    I hope this helps. Thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,203
    Those ranges are different sizes and shapes.
    That will produce unpredictable results.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    09-03-2008
    Location
    california
    Posts
    3
    Do you mean different sizes because of the blank cells? I have the array looking into a 2 row by 8 column area, but some of that data hasn't been inputted yet.

    Here's a cleaner version just incase my random placement is a problem.
    Attached Files Attached Files

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,203
    I mean that in the formula, A$12:$A$13 is a 2 X 1 range, $B$11:$G$11 is a 1 X 6 and !$B$12:$G$ is 2 X 6

  7. #7
    Registered User
    Join Date
    12-08-2008
    Location
    NYC
    MS-Off Ver
    2003 & XP
    Posts
    43

    Here ya go

    don't why you had the if statement there, not sure what you were gettting at but you missed a very important "if" in the array sumIF formula.

    See file attached for your answer. Please note that i switched your order for the logic test. I prefer to see it Range=Criteria but the way you had it.....Criteria=Range will work as well.


    Enjoy
    Attached Files Attached Files

+ 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. assistance with array formula
    By neowok in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2008, 05:00 PM
  2. Easiest one-cell formula for multiplying array elements
    By kayard in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-27-2008, 03:55 AM
  3. Array Formula
    By djfatboyfats in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-06-2007, 08:16 PM
  4. Replacing "0" values with null string in array while preserving cell formula
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-29-2007, 01:07 PM
  5. Inserting Array Formula
    By Jaymond Flurrie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-12-2007, 11:08 AM

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