Code doesnt work

[KnAsTa]

=IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),)

When the ISNUMBER(MATCH) function returns true, it gives me #N/A results in the cells which should have 1 or 0 in them.

 

[Dave Patton]

What do you mean by "Tab delimited"?

For the formulas to work, the information must be in regular Excel cells.

Vlookup looks up from the left most column; consequently, you must revise your formula
"VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0) "

to VLOOKUP(A5,week1.xls!$B$1:$D$10,2,0)

Note. the formula that I suggested works.
=AND(B1>"",COUNTIF(LookupD1.xls!rL3_,B1))*VLOOKUP(B1,LookupD1.xls!rL3_,5,0)

The range is relevant and the offset is one less. The win or loss is calculated on the Lookup table.

 

[Dave Patton]

Another approach that has some advantages follows:

=SUMPRODUCT(('[LookupD1.xls]1'!$A$2:$A$10=B1)*('[LookupD1.xls]1'!$E$2:$E$10))+SUMPRODUCT(('[LookupD1.xls]1'!$B$2:$B$10=B1)*('[LookupD1.xls]1'!$F$2:$F$10))

This looks for the team (B1) in the range (A2:A10) if yes it returns the value in E2:E10)
and it looks for the same team in (B2:B10) and if Yes it return the value in F2:F10.

If the team won the result would be either
1+0=1 or 0+1=1

If the team lost, the result would be 0+0.

If you name the ranges of the external file and edit the formula, it can be condensed to

=SUMPRODUCT((rA=B1)*(rE)+(rB=B1)*(rF))
This message was edited by Dave Patton on 2002-01-14 15:03

 

[KnAsTa]

Ok, i'm still stuck, i have gained some advances from all your help, thanx everyone, but i think i'm going to have to explain the situation better.

I have a main spreadsheet with 19 Soccer teams listed from A5 to A24. I am typing in a formula into cell M5 which is "Away wins".

This formula takes data from an external spreadsheet called week1.xls. And it contains 4 columns and 10 rows. From A1 to B10 holds all the matches (ie all the teams paired up)
ex:
Everton Leeds 3 4
Chelsea West Ham 0 5
Southhampton Middlesbrough 2 5
Aston Villa Leicester 0 4
Liverpool Bolton 6 0
Fulham Newcastle 2 2
Sunderland Tottenham 4 3
Arsenal Blackburn 0 5
Charlton Man Utd 6 1
Derby Ipswich 3 2

The formula i am using is "supposed" to check if A5(from the main spreadsheet - which is a soccer teams name ie Arsenal) is in the external spreadsheet in $B$1:$B$10 and returns true or false if it is or isnt. If it returns true it will then calculate if they won. Ie if column 3 < column 4 (goals scored by team in column A < goals scored by team in column B)

here is my formula:
=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),"")

I have week1.xls open aswell so it does not need a hard coded address.

I have also written another formula very similar to this which checks if the team in column A in external spreadsheet wins. And this formula works

Working formula:
=IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),"")

So the formula that does not work has only 1 thing changed in it. It changes from $A$1:$A$10 to $B$1:$B$10 . I CANNOT SEE HOW THIS DOES NOT WORK!

The non working formula returns correctly for the false clause, but returns #N/A for the true clause.

If anyone can be bothered reading all this, i am very greatful, thanx.

 

[ALBUNDY]

its not a real reply it just me I thought Id be a ****

 

[Ian Mac]

On 2002-04-15 01:28, ALBUNDY wrote:
its not a real reply it just me I thought Id be a

I wish I hadn't bothered answering your question AL, leave the langauge in the school yard. Also both of you, it's NOT CODE it a FORMULA!!!!

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-15 02:19

 

[Ian Mac]

On 2002-04-15 01:10, KnAsTa wrote:
Ok, i'm still stuck, i have gained some advances from all your help, thanx everyone, but i think i'm going to have to explain the situation better.

I have a main spreadsheet with 19 Soccer teams listed from A5 to A24. I am typing in a formula into cell M5 which is "Away wins".

This formula takes data from an external spreadsheet called week1.xls. And it contains 4 columns and 10 rows. From A1 to B10 holds all the matches (ie all the teams paired up)
ex:
Everton Leeds 3 4
Chelsea West Ham 0 5
Southhampton Middlesbrough 2 5
Aston Villa Leicester 0 4
Liverpool Bolton 6 0
Fulham Newcastle 2 2
Sunderland Tottenham 4 3
Arsenal Blackburn 0 5
Charlton Man Utd 6 1
Derby Ipswich 3 2

The formula i am using is "supposed" to check if A5(from the main spreadsheet - which is a soccer teams name ie Arsenal) is in the external spreadsheet in $B$1:$B$10 and returns true or false if it is or isnt. If it returns true it will then calculate if they won. Ie if column 3< column 4 (goals scored by team in column A< goals scored by team in column B)

here is my formula:
=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),"")

I have week1.xls open aswell so it does not need a hard coded address.

I have also written another formula very similar to this which checks if the team in column A in external spreadsheet wins. And this formula works

Working formula:
=IF(ISNUMBER(MATCH(A5,week1.xls!$A$1:$A$10,0)),IF(VLOOKUP(A5,week1.xls!$A$1:$D$10,3,0)>VLOOKUP(A5,week1.xls!$A$1:$D$10,4,0),1,0),"")

So the formula that does not work has only 1 thing changed in it. It changes from $A$1:$A$10 to $B$1:$B$10 . I CANNOT SEE HOW THIS DOES NOT WORK!

The non working formula returns correctly for the false clause, but returns #N/A for the true clause.

If anyone can be bothered reading all this, i am very greatful, thanx.

Now I get it:

OK.

you need to change the $A$1:$D$10 to $B$1:$D$10. because the match works look at B:B your then looking at A currently, which means of course it won't find the Team because it in B.

Use:

=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$B$1:$D$10,3,0)<VLOOKUP(A5,week1.xls!$B$1:$D$10,2,0),3,0),"")

of course now you need to do the Draw.


_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-15 02:43

 

[KnAsTa]

Wicked, thanx heaps Ian, your a champion. It worked! Thanks for taking the time to help us, it is seriously appreciated greatly by both me and ALBUNDY.

 

[KnAsTa]

Ok, now that we have the working formula getting values from week1.xls we need it to get it from 19 files of the name week1.xls , week2.xls , week3.xls .... up to week19.xls.

Can we use a For loop or something of the sort in the formula, or is there another way of doing it?

Like for example in this formula:
=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$B$1:$D$10,2,0)>VLOOKUP(A5,week1.xls!$B$1:$D$10,3,0),1,0),)

we need it to go through all the files and continually adding up the wins so we end up with a total number of wins for the 19 weeks.

 

[Ian Mac]

On 2002-04-15 02:55, KnAsTa wrote:
Ok, now that we have the working formula getting values from week1.xls we need it to get it from 19 files of the name week1.xls , week2.xls , week3.xls .... up to week19.xls.

Can we use a For loop or something of the sort in the formula, or is there another way of doing it?

Like for example in this formula:
=IF(ISNUMBER(MATCH(A5,week1.xls!$B$1:$B$10,0)),IF(VLOOKUP(A5,week1.xls!$B$1:$D$10,2,0)>VLOOKUP(A5,week1.xls!$B$1:$D$10,3,0),1,0),)

we need it to go through all the files and continually adding up the wins so we end up with a total number of wins for the 19 weeks.

See my answer to AL? if you could keep it one post that'd be better for us all. I'll continue with the thread that gets a reply from either of you.