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>

 

[Aladin Akyurek]

Thanks Aladin...

Just to make sure I did have to enter your solution with CSE... Is that right?

You are welcome. Yes, we need CSE... From an audit point of view, resorting
to a SumProduct expression here is not a good idea.

 

[How_Do_I]

Thanks Aladin...

 

[How_Do_I]

The following would be admissible on Excel 2003...

=LOOKUP(BigNum,CHOOSE({1,2},0,MATCH(1,IF(Home=E2,IF(Away=F2,1)),0)))

where BigNum = 9.99999999999999E+307 or just 9.99E+307.

Note. The IF form is slightly faster than binary multiplications.

Aladin,

I just seen that I have major problems with this formula... Basically if the "Home" and "Away" haven't played a game the formula returns "0" as my ref row... The fact that it returns a "0" then creates problems later in my book when I pull things using SMALL...

Without a double calculation and bearing in mind I'm using xl2003, is there a way of returning a blank if "Home" and "Away" don't return a ref number please?

 

[Aladin Akyurek]

Aladin,

I just seen that I have major problems with this formula... Basically if the "Home" and "Away" haven't played a game the formula returns "0" as my ref row... The fact that it returns a "0" then creates problems later in my book when I pull things using SMALL...

Without a double calculation and bearing in mind I'm using xl2003, is there a way of returning a blank if "Home" and "Away" don't return a ref number please?

1) Suppose that the formula in question is in X2 and its result is 0.

2) Let's also suppose that we want to use the value from X2 in an Index/Small formula in Y2... The following would be a general approach:

Y2:

=IF(X2,the Index expression,"")

If this is not clear, would you post the formula expression using the result from the LOOKUP formula under consideration?

 

[How_Do_I]

Hi Aladin,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I think I understand you. The problem is, so much of my workbook is dependant on these ref numbers. The “0” return is my problem as I reverted back to my original double calculating formula and that eliminated my problem…
<o></o>
I’ll paste you the following formulas to give you an idea of how my ref number is so vital…
<o></o>
These are formulas to find Last 5 games…
<o></o>

Excel Workbook
	BM	BN
5	Last 5 Home Played	5
6	Last 5 Home Min	444
7	Last 5 Home Max	541
8	Last 5 Away Played	5
9	Last 5 Away Min	438
10	Last 5 Away Max	537
11
12	Barnsley	0
13	Bristol City	32
FT Scores

#VALUE!

</TD></TR></TABLE></TD></TR></TABLE>


<o></o>
To find Last 8 games
<o></o>

Excel Workbook
	BM	BN
47	Last 8 Game 1	541
48	Last 8 Game 2	537
FT Scores

#VALUE!

</TD></TR></TABLE></TD></TR></TABLE>


<o></o>
To find since last Draw for example…
<o></o>

Excel Workbook
	CM	CN
7	D	2
8	HomeSinceLastDraw	1
9
10
11
12		W
13		D
FT Scores

#VALUE!

</TD></TR></TABLE></TD></TR></TABLE>


<o></o>
There are lots of other things that are dependant on my ref number too…

 

[Aladin Akyurek]

Care to post a double calculating formula which depends on what the LOOKUP formula
under consideration returns?

 

[How_Do_I]

I’m not too sure what you mean… I think the issue is the LOOKUP method when returning “0”.

0 “confuses” my book because it sees “0 ref” as a game which my book thinks has been played.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
These are the two formulas side by side…
<o></o>
LOOKUP version:
<o></o>

Excel Workbook
	BM	BN
12	Barnsley	0
FT Scores

#VALUE!

</TD></TR></TABLE></TD></TR></TABLE>


<o></o>
Double Calculating:
<o></o>

Excel Workbook
	BH	BI
12	Barnsley
FT Scores

#VALUE!

</TD></TR></TABLE></TD></TR></TABLE>


<o></o>
Thanks Aladin, I hope that is what you are after.

 

[Aladin Akyurek]

Which formula uses the result in BN12, which houses the LOOKUP formula?

 

[How_Do_I]

Here is one column of the grid that pulls all the ref number…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Those numbers in a LARGE will give away games in last played order (today, yesterday etc…)… or in a SMALL the games in date order (1<SUP>st</SUP> game of the season, 2<SUP>nd</SUP> game of the season etc)
<o></o>
When the game hasn’t been played I yield a 0 … Barnsley can’t play <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Barnsley</st1lace> so a “0”… Barnsley haven’t played Watford or <st1lace w:st="on">Middlesbrough</st1lace> so a “0”…
<o></o>
Other formulas that are reliant on my ref number like the examples in post # 15 are not calculating properly… because they see “0” as a game played
<o></o>
What I refer to as my double calculating formula yield “” if a game hasn’t been played… so everything works…
<o></o>
I will stick with the double calculating if that is the only solution Aladin…

Excel Workbook
	BM	BN
1		Barnsley
2		1
3	Season Home	23
4	Season Away	21
5	Last 5 Home Played	5
6	Last 5 Home Min	444
7	Last 5 Home Max	541
8	Last 5 Away Played	5
9	Last 5 Away Min	382
10	Last 5 Away Max	537
11
12	Barnsley	0
13	Bristol City	32
14	Burnley	140
15	Cardiff City	438
16	Coventry City	147
17	Crystal Palace	464
18	Derby County	407
19	Doncaster Rovers	321
20	Hull City	302
21	Ipswich Town	195
22	Leeds Utd	382
23	Leicester City	344
24	Middlesbrough	0
25	Millwall	324
26	Norwich City	53
27	Nottingham Forest	281
28	Portsmouth	378
29	Preston North End	187
30	QPR	6
31	Reading	88
32	Scunthorpe United	102
33	Sheffield United	537
34	Swansea City	270
35	Watford	0
FT Scores

[/B]

 

[Aladin Akyurek]

I think I dislike Jeanie... Thwarts communication.

Here is an example what I mean...

This LOOKUP bit we are taking about calculates a position. Let's suppose
that such a formula resides in X2.

Let's suppose that I need to use X2 in an INDEX formula in Y2...

Since X2 can be either a 0 or a non-zero number, I can do the following in Y2:

=IF(X2=0,"",INDEX(Range,X2))

This formula avoids using 0 and gives a blank.

This is why I was asking you which formula directly uses the result of the LOOKUP formula...