Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Formulae / LOOKUP help

  1. #1
    Acolyte Beddy Boy's Avatar
    Join Date
    Feb 2017
    Posts
    28
    Articles
    0
    Excel Version
    Excel 365

    Formulae / LOOKUP help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I would really appreciate some help in setting up a spreadsheet.

    I have a table with pupils ID numbers followed by a score for a test in the adjacent column.

    P T1 P T2 P T3
    2 3 3 2 1 1.5
    4 4 5 4 2 15
    5 8 7 8 5 9.5
    7 9.5 9 9 7 6
    9 15 10 11 11 12
    11 12 14 15


    I wish to set up a table where pupils ID numbers are set out in a table automatically depending on their score - A 0-5, B 5-10, C > 10

    A B C
    T1 2,4 5,7 9
    T2 3,5 7,9 10,11
    T3 1 5,7 2,11,14


    Then following this, Another table where the average scores are displayed.

    A B C
    T1 3.5 9 15 etc. etc.

    Please can anyone help with coding for this?

    Many thanks,

    Wayne

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    Save us time and attach a workbook with the first table in, making it as true-to-life as you can without revealing sensitive data.

  3. #3
    Acolyte Beddy Boy's Avatar
    Join Date
    Feb 2017
    Posts
    28
    Articles
    0
    Excel Version
    Excel 365
    Quote Originally Posted by p45cal View Post
    Save us time and attach a workbook with the first table in, making it as true-to-life as you can without revealing sensitive data.
    Thank you for the response.

    Please see file.

    Please let me know if you need anymore info.
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    In the attached:

    • List (Table 1) created at cell A13 can be recreated by clicking the button at cell F11
    • Table 2 at cell I13 uses formula in range J14, copied down and across. This formula is ARRAY-ENTERED.
    • Table 3 at cell N12 is a simple pivot table.
    Attached Files Attached Files

  5. #5
    Acolyte Beddy Boy's Avatar
    Join Date
    Feb 2017
    Posts
    28
    Articles
    0
    Excel Version
    Excel 365
    Thank you very much for your help!

    If I click create table then table 2 is populated correctly. However, table 3 doesn't change.

    For example, if all the results in Test 1 for all pupils are 3, the average for Test 1 (with scores 0-5) in Cell 014, would be 3. Unfortunately this doesn't change at the moment.

    Thanks again,

    Wayne

  6. #6
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    37
    Articles
    0
    Excel Version
    2016

    Lightbulb

    Based on provided example spreadsheet

    J2=TEXTJOIN(",",,IF((OFFSET($A$2,,MATCH($I2,$B$1:$F$1,),6,1)>=SUBSTITUTE(LEFT(J$1,2),"-",)-0)* (OFFSET($A$2,,MATCH($I2,$B$1:$F$1,),6,1)<=SUBSTITUTE(RIGHT(J$1,2),"-",)-0)*(OFFSET($A$2,,MATCH($I2,$B$1:$F$1,),6,1)<>""),OFFSET($A$2,,MATCH($I2,$B$1:$F$1,)-1,6,1),"")) copy to L4

    O2=AVERAGE(IF((OFFSET($A$2,,MATCH($N2,$B$1:$F$1,),6,1)>=SUBSTITUTE(LEFT(O$1,2),"-",)-0)* (OFFSET($A$2,,MATCH($N2,$B$1:$F$1,),6,1)<=SUBSTITUTE(RIGHT(O$1,2),"-",)-0)*(OFFSET($A$2,,MATCH($N2,$B$1:$F$1,),6,1)<>""),OFFSET($A$2,,MATCH($N2,$B$1:$F$1,),6,1),"")) copy to Q4 (wondering how you get "10.5" in Q3, (11+12)/2=10.5??)
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.JPG 
Views:	7 
Size:	71.3 KB 
ID:	9584  

  7. #7
    Acolyte Beddy Boy's Avatar
    Join Date
    Feb 2017
    Posts
    28
    Articles
    0
    Excel Version
    Excel 365
    Yeah sorry, Q3 - that's a typo...

    Sorry for my ignorance but your Table 3 does not change if I change the scores in Table 1 and re-create the list.

    Am I suppose to run something to get your Table 3 to update?

    Thanks again
    Last edited by Beddy Boy; 2020-01-24 at 09:05 PM.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,681
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Beddy Boy View Post
    Table 3 does not change if I change the scores in Table 1 and re-create the list.

    Am I suppose to run something to get your Table 3 to update?
    I mentioned it's a pivot table; it needs refreshing: right-click it and choose Refresh.
    Because it's a pivot table, if the size of source data changes, the source range of the pivot table needs to match the new list.
    That and the refreshing of the pivot can be done automatically if you want. The only thing is, I've put the list beneath the original source data, and if that source data gets much bigger, it will overlap the new list. Of copurse, the new list can be placed anywhere.
    Last edited by p45cal; 2020-01-24 at 11:05 PM.

  9. #9
    Acolyte Beddy Boy's Avatar
    Join Date
    Feb 2017
    Posts
    28
    Articles
    0
    Excel Version
    Excel 365
    Great. Thank you so much!

  10. #10
    Acolyte Beddy Boy's Avatar
    Join Date
    Feb 2017
    Posts
    28
    Articles
    0
    Excel Version
    Excel 365
    So how could I get them to automatically update when I change the data in table 1?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •