Code doesnt work - Page 2
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Code doesnt work

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,331
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    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.

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,331
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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 ]

  3. #13
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #14
    New Member
    Join Date
    Apr 2002
    Location
    Aust
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #16
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #17
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #18
    Board Regular
    Join Date
    Apr 2002
    Location
    Australia
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #19
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.
    "Have a good time......all the time"
    Ian Mac

User Tag List

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