Double calculating Formulas xl2003

[How_Do_I]

In my Jeanie I’m showing two ways of getting the “Ref” number (G2 and G3) for my selected teams (E2 and F2)…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
What I was to know is… in xl 2003 is there a way to do this without the double calculating formula to eliminate the N/A?
<o> </o>
Also how could I use utilize SUMPRODUCT as opposed to CONCATENATION in my “Non Array” solution please?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Ref	Home	Away				Ref
2	1	Norwich City	Watford		Burnley	Nottingham Forest	8	Array
3	2	Bristol City	Millwall				8	Non Array
4	3	Coventry City	Portsmouth
5	4	Hull City	Swansea City
6	5	Preston North End	Doncaster Rovers
7	6	QPR	Barnsley
8	7	Reading	Scunthorpe United
9	8	Burnley	Nottingham Forest
10	9	Crystal Palace	Leicester City
11	10	Middlesbrough	Ipswich Town
12	11	Leeds Utd	Derby County
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[How_Do_I]

Hi Aladin,

I have 24 Home teams and 24 Away teams...

That range in my book has been named "Grid"... In my last post I put the first column of "Grid" in my Jeanie...

So one of the many formulas that "uses" BN12 is...

=IF(ROWS(BN$47:BN47)>BN$55,"",LARGE((INDEX(Grid,0,BN$2),INDEX(Grid,BN$2,0)),ROWS(BN$47:BN47)))

That is taking all the home games and the away games for ONE team...

There isn't a formula that uses BN12 in the context that you have asked... My formula will use the rows and or columns within a range named "Grid" that BN12 falls in.

I hope that explains this...?

 

[Aladin Akyurek]

Hi Aladin,

I have 24 Home teams and 24 Away teams...

That range in my book has been named "Grid"... In my last post I put the first column of "Grid" in my Jeanie...

So one of the many formulas that "uses" BN12 is...

=IF(ROWS(BN$47:BN47)>BN$55,"",LARGE((INDEX(Grid,0,BN$2),INDEX(Grid,BN$2,0)),ROWS(BN$47:BN47)))

That is taking all the home games and the away games for ONE team...

There isn't a formula that uses BN12 in the context that you have asked... My formula will use the rows and or columns within a range named "Grid" that BN12 falls in.

I hope that explains this...?

Am I understanding correctly that you want "", not a 0 from that LOOKUP formula in BN12? If so, and we are on an Excel version prior to 2007...

Insert the following code as a module to you workbook:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Now we can have something like:

=IF(ISNUMBER(V(MATCH(1,IF(Home=$BE12,IF(Away=BF$1,1,"")),0))),V(),"")

which must be confirmed with control+shift+enter, not just with enter.

 

[How_Do_I]

Yes… don’t give me a zero!!! Brilliant Aladin that has cracked it… Thank you so much for sticking with me on this one…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Could I just ask one more of these questions please… That has sorted out my ref number for 552 matches in the season…
<o></o>
My final double calculation is looking at the result of Team A v Team B and then Team B v Team A (the home game and the away games)…
<o></o>
The first Jeanie is the old method…
<o></o>
Output

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 33px"><COL style="WIDTH: 141px"><COL style="WIDTH: 30px"><COL style="WIDTH: 30px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AM</TD><TD>AN</TD><TD>AO</TD><TD>AP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">109</TD><TD style="BACKGROUND-COLOR: #ffff00">Norwich City</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">L</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">133</TD><TD style="BACKGROUND-COLOR: #ffff00">Reading</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">W</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">L</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">145</TD><TD style="BACKGROUND-COLOR: #ffff00">QPR</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">D</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">D</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AP10</TD><TD>{=IF(ISNA(INDEX(Away_FT,MATCH(1,(Home=AN10)*(Away=AO$4),0))),"",INDEX(Away_FT,MATCH(1,(Home=AN10)*(Away=AO$4),0)))}</TD></TR><TR><TD>AP11</TD><TD>{=IF(ISNA(INDEX(Away_FT,MATCH(1,(Home=AN11)*(Away=AO$4),0))),"",INDEX(Away_FT,MATCH(1,(Home=AN11)*(Away=AO$4),0)))}</TD></TR><TR><TD>AP12</TD><TD>{=IF(ISNA(INDEX(Away_FT,MATCH(1,(Home=AN12)*(Away=AO$4),0))),"",INDEX(Away_FT,MATCH(1,(Home=AN12)*(Away=AO$4),0)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4
<o></o>
The next Jeanie is my attempt to use your formula… this should give W D or L if the match has been played and blank if it hasn’t…. It is basically working apart from the #value error in AP35
<o></o>
Both Jeanies are looking at the same home and away teams… (Home=AN10 Away=AO$4 etc)... They have both pulled the correct L and D, the problem is how the new formulas handle the error... Any ideas please Aladin?
<o></o>
Output

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 30px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD>###</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD>D</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>AP35</TD><TD>{=INDEX(Away_FT,IF(ISNUMBER(V(MATCH(1,IF(Home=AN10,IF(Away=AO$4,1,"")),0))),V(),""))}</TD></TR><TR><TD>AP36</TD><TD>{=INDEX(Away_FT,IF(ISNUMBER(V(MATCH(1,IF(Home=AN11,IF(Away=AO$4,1,"")),0))),V(),""))}</TD></TR><TR><TD>AP37</TD><TD>{=INDEX(Away_FT,IF(ISNUMBER(V(MATCH(1,IF(Home=AN12,IF(Away=AO$4,1,"")),0))),V(),""))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

=IF(ISNA(INDEX(Away_FT,MATCH(1,(Home=AN10)*(Away=AO$4),0))),"",INDEX(Away_FT,MATCH(1,(Home=AN10)*(Away=AO$4),0)))

===>

=IF(ISNA(V(MATCH(1,(Home=AN10)*(Away=AO$4),0))),"",INDEX(Away_FT,V()))

which you can copy down. Don't forget the control+shift+enter bit though, instead of what you have in AP35.

 

[How_Do_I]

That works brilliantly Aladin. Thank you so much for all your help on this one.

 

[Aladin Akyurek]

That works brilliantly Aladin. Thank you so much for all your help on this one.

You are welcome. Thanks for providing feedback.