Results 1 to 6 of 6
  1. #1
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442

    Excel Issue

    I don't want to create a user on an excel forum so I'm posting this here first

    This is my spreadsheet

    Score Week Card Person 1 2 3 4 5 6 7 8 9 Total
    1 1 Person A 4 4 4 4 4 4 4 4 4 36
    1 1 Person C 3 3 3 3 3 3 3 3 3 27
    1 1 Person D 5 5 5 5 5 5 5 5 5 45
    1 1 Person E 4 5 4 5 4 5 4 5 4 40
    1 1 Person B 6 5 6 5 6 5 6 5 6 50
    1 1 Person F 5 6 5 6 5 6 5 6 5 49
    2 2 Person A 4 5 4 5 4 5 4 5 4 40
    3 3 Person A 5 5 5 5 5 5 5 5 5 45
    2 2 Person B 6 6 6 6 6 6 6 6 6 54


    I have this on a tab called "DATA"
    ON a second tab called "OUTCOME" I want to look up the maximum "SCORE" value based on the person name
    Assuming the cell holding the person name is in G3 on the tab "OUTCOME" I am using the formula

    =MAX(IF(Data!$C$2:$C$10=$G$3,Data!$A$2:$A$10,0))

    I have a number of problems with this formula
    If I use it on the DATA tab, it works, but only for only Person A (returning the value of 3), for all other people it returns 0
    If I use it on the OUTCOME tab, it doesn't work at all (returns 0 for everyone)

    Even if I hard code the Person name

    =MAX(IF(Data!$D$2:$D$10="Person B",Data!$A$2:$A$10,0))

    The formula returns a 0



    Or if I try to concantenate the expression

    =MAX(IF(Data!$D$2:$D$10=""&$G$3&"",Data!$A$2:$A$10 ,0))

    If anyone has a suggestion before I go create an excel forum user please let me know.

    p.s. I despise excel....

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What comes to mind is the VLOOKUP, HLOOKUP and MATCH functions. I'm no XL wizard either. I've forgotten a lot of this stuff even though I've used it many times.
    Maybe if I play with it a bit...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    result of =VLOOKUP(A2,DATA!D2:N10,11)
    Person SCORE
    Person A 45
    Person C 27
    Person D 45
    Person E 40
    Person B 54
    Person F 49

    Source table is supposed to be sorted ascending, I believe. At least I did.
    Last edited by Micron; 04-01-2017 at 05:05 PM. Reason: fixed table
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This also seems to work but the person must be sorted ascending (as in my prior post, which I was alluding to) but for this one, the values must be sorted descending. I tried wrapping this in the MAX function, but probably didn't write that part correctly. Hence the values had to be descending.

    Interesting; I can only put a lower case d with D: otherwise I get smilies and can't figure out how to escape the combination.

    =INDEX(DATA!D:N,MATCH(A2,DATA!D:d,0),11)
    =INDEX(DATA!D:N,MATCH(A3,DATA!D:d,0),11)
    =INDEX(DATA!D:N,MATCH(A4,DATA!D:d,0),11)
    and so on. 11 is the column number of the table or array.
    DATA TAB
    Score Week Card Person 1 2 3 4 5 6 7 8 9 Total
    1 1 Person A 4 4 4 4 4 4 4 4 4 36
    2 2 Person A 4 5 4 5 4 5 4 5 4 40
    3 3 Person A 5 5 5 5 5 5 5 5 5 45
    1 1 Person B 6 5 6 5 6 5 6 5 6 50
    2 2 Person B 6 6 6 6 6 6 6 6 6 54
    1 1 Person C 3 3 3 3 3 3 3 3 3 27
    1 1 Person D 5 5 5 5 5 5 5 5 5 45
    1 1 Person E 4 5 4 5 4 5 4 5 4 40
    1 1 Person F 5 6 5 6 5 6 5 6 5 49
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ... stupid sorts I thought I what I had was correct but clearly not! Thanks micron

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    U R welcome.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-02-2015, 04:18 PM
  2. excel binding issue i think...
    By trevor40 in forum Import/Export Data
    Replies: 2
    Last Post: 02-04-2015, 04:47 PM
  3. Data Upload issue from Excel to Access
    By shabar in forum Access
    Replies: 2
    Last Post: 02-05-2013, 12:49 AM
  4. Issue with importing excel data
    By Jrbeene86 in forum Import/Export Data
    Replies: 0
    Last Post: 03-27-2012, 08:32 PM
  5. Issue recognizing Excel 'ranges'
    By Captain Database ...!! in forum Import/Export Data
    Replies: 4
    Last Post: 07-18-2011, 02:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums