Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Array Formula and Charts

This is a discussion on Array Formula and Charts within the Excel Questions forums, part of the Question Forums category; I am using Excel 2003. I have Spreadsheet#1 which has Names and Dates in columns as below. A B Name ...

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    7

    Default Array Formula and Charts

    I am using Excel 2003.
    I have Spreadsheet#1 which has Names and Dates in columns as below.

    A B
    Name | Date

    I have Spreadsheet#2 which contains Date, Sales and Results for each Name as below and is exported from a database in this format.

    A B C D E F
    Name Name
    Date | Sales | Results | Date | Sales | Results

    In Spreadsheet #2, the I have tried writing an array formula in the Results column to enter the value from the Sales column into the Results column if the Name in cell A1 equals the Name in column A of Spreadsheet#1 and the Date equals the Date in Spreadsheet#1. When the array formula returns a false result (name and date do not match), the formula returns zero as the answer. Is there a way I can have a blank result for false? The reason I need a blank is because I am graphing the data in Spreadsheet#2 and I donít want all the false results (the zeros) appearing on my graph.

    I have looked at several sites online and have not been able to find a solution. Any help would be appreciated.
    Thankyou!!

  2. #2
    Board Regular Rastaman's Avatar
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    210

    Default Re: Array Formula and Charts

    Can you post the formula you have in the results column? It might be as simple as adding an IF statement to the beginning of the formula. Something like =IF(array formula=0,"",array formula). This does double the calculation load, though, which may impact performance. There could be some simpler options, though.

    Rick
    Whoa, another split! What a bummer!

    Excel 2013, Win7

  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    7

    Default Re: Array Formula and Charts

    Rastaman - appreciate your response. This is my formula. I even tried using NA() as the result for false rather than the double quotations, but then all I return in every cell was #N/A. If I could only get the #N/A times when both conditions are not met, I'd be happy because Excel does not chart #N/A as text or zero. Here is my formula that is in Spreadsheet#2 Results column where B2 is the Name and A778 is the Date and B778 is Sales. This was entered using Ctrl Shift Enter.
    =SUM(IF(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A778='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640),B778,""))

    Thankyou!!

  4. #4
    Board Regular Rastaman's Avatar
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    210

    Default Re: Array Formula and Charts

    I don't quite understand your data, but here's what I was suggesting. Basically a repeat of your formula (I rewrote without the IF), first check if the result is zero, if so set to blank, or you could replace the "" with NA().

    Regarding your formula, is it giving you the correct result except for your need to show blank instead of zero? It doesn't look like it's summing anything, it's just multiplying the value in cell B778 times the number of times the name/date test is true. Don't you want B778 to instead be the the range of the Results column? I am also assuming that Sheet#1 column A is Names, and Sheet#1 column I is dates.



    Code:
    =IF(SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A778='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B778)=0,"",SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A778='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B778))
    Whoa, another split! What a bummer!

    Excel 2013, Win7

  5. #5
    New Member
    Join Date
    Dec 2010
    Posts
    7

    Default Re: Array Formula and Charts

    Thanks for suggestion, but unfortunately it still does not work. It makes Results column appear blank when it should contain the value from the Sales column and also on the chart it appears as a value of 0 because the formula is still there.
    Will try to explain more.
    Formula in Results column of Spreadsheet#2 is checking for two conditions, the Date and the Name in Spreadsheet#1 to equal the Date and Name in Spreadsheet#2 and if they are both the same (true), then enter the Sales value from Spreadsheet#2 into the Results column for that date and if the Name and/or Date do not equal the Name and Date from Spreadsheet#1, leave the cell blank or put #N/A. The problem is, using the double quotations leaves zeros in the results column whether you use them as the result for true or false. If I use the NA(), the formula doesn't return the Sales value into the Results column.

  6. #6
    Board Regular Rastaman's Avatar
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    210

    Default Re: Array Formula and Charts

    Princess,
    Does my example below look anything like your data? I've tried to recreate it based on your description. In this example you enter the name into cell B2 of sheet#2, and the values in the Results column update to show the Sales if name and date match Sheet#1. If there is no match, the cell in the results column C is blank.

    Rick



    Sheet#2

     ABC
    2namejane 
    3datesalesresults
    41/1/20101212
    51/2/201010 
    61/3/20101414

    Spreadsheet Formulas
    CellFormula
    C4{=IF(SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A4='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B4)=0,"",SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A4='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B4))}
    C5{=IF(SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A5='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B5)=0,"",SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A5='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B5))}
    C6{=IF(SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A6='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B6)=0,"",SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A6='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B6))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

    Sheet#1

     ABCDEFGHI
    6Name       Date
    7joe       1/1/2010
    8tom       1/1/2010
    9jane       1/1/2010
    10fred       1/1/2010
    11joe       1/2/2010
    12tom       1/2/2010
    13fred       1/2/2010
    14joe       1/3/2010
    15jane       1/3/2010
    16fred       1/3/2010


    Excel tables to the web >> Excel Jeanie HTML 4
    Whoa, another split! What a bummer!

    Excel 2013, Win7

  7. #7
    New Member
    Join Date
    Dec 2010
    Posts
    7

    Default Re: Array Formula and Charts

    Rick/Rastaman - Thankyou! It does appear to be working which is great! I guess the only problem still is that the chart in Sheet#2. It will put markers on the chart with a value of zero for all the blanks and the only way to get around this is the use NA() instead of "". I couldn't get this to work before, but will try again using your formula format. Hope it works!
    Cheers,
    PrincessD

  8. #8
    New Member
    Join Date
    Dec 2010
    Posts
    7

    Default Re: Array Formula and Charts

    Rastaman - I modified the formula you gave me to have NA() instead of "" and everything works great!! THANKYOU!!!
    PrincessD

  9. #9
    New Member
    Join Date
    Dec 2010
    Posts
    7

    Default Re: Array Formula and Charts

    Rastaman, sorry to bug you again, but I found a glitch....using your example, if the sales value is zero, this formula won't copy over the zero into the results column. Any idea on how to add that factor into the formula?
    Thanks,
    PrincessD

  10. #10
    Board Regular Rastaman's Avatar
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    210

    Default Re: Array Formula and Charts

    Hi PrincessD. No worries, I think the formula below will work. I removed the '*B4' in the IF test, this isn't necessary and was causing the test to think there was no match for the name/date. I included the NA() in this version.

    Rick


    entered with control-shift-enter, copied down over the range:
    Code:
    =IF(SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A4='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640))=0,NA(),SUM(($B$2='[Spreadsheet#1.xls]Sheet#1'!$A$7:$A$1640)*(A4='[Spreadsheet#1.xls]Sheet#1'!$I$7:$I$1640)*B4))
    Whoa, another split! What a bummer!

    Excel 2013, Win7

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com