Array Formula and Charts

[PrincessD]

I am using Excel 2003.
I have Spreadsheet#1 which has Names and Dates in columns as below.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
A B
Name | Date
<o> </o>
I have Spreadsheet#2 which contains Date, Sales and Results for each Name as below and is exported from a database in this format.
<o> </o>
A B C D E F
Name Name
Date | Sales | Results | Date | Sales | Results
<o> </o>
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!!

 

[Rastaman]

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

 

[PrincessD]

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!!

 

[Rastaman]

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.

=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))

 

[PrincessD]

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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Will try to explain more. <o></o>
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.<o></o>

 

[Rastaman]

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

Excel Workbook
	A	B	C
2	name	jane
3	date	sales	results
4	1/1/2010	12	12
5	1/2/2010	10
6	1/3/2010	14	14
Sheet#2

Excel Workbook
	A	B	C	D	E	F	G	H	I
6	Name								Date
7	joe								1/1/2010
8	tom								1/1/2010
9	jane								1/1/2010
10	fred								1/1/2010
11	joe								1/2/2010
12	tom								1/2/2010
13	fred								1/2/2010
14	joe								1/3/2010
15	jane								1/3/2010
16	fred								1/3/2010
Sheet#1

 

[PrincessD]

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

 

[PrincessD]

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

 

[PrincessD]

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

 

[Rastaman]

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:

=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))