Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Combining If... Vlookup... Offset... in a formula

    I am trying to look down a list of names and results over a period and put in a table a count of the number of times people have got an end score higher than the first score. Plus Lower than and same as in different counts.

    My brain now hurts and I ask if someone can help please?

    I have attached a copy.

    Thanks

    Peter
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't think it can be done directly with formulas but would require an intermediate setup. I created a pivot table and then got the results from the pivot table

    Steve
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks Steve
    looks good, but I need to compare the last result, which may be Closing, Review1, 2 or 3.

    What you've done certainly gets me thinking thanks.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In G5 you can enter:
    =MATCH(9E+300,C5:F5)

    and copy G5 from G6 to G34, this gives the last entry for that name. Then change the formulas in I2:K2 from Col C to Col G...

    Steve

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks again Steve

    I have it now. Please tell me what is the 9E+300?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    9E+300 is just a very large number (9 followed by 300 zeroes). The match "trick"/technique used tries to find this number and when it can't it quits at the end of the list: the last number in the range.

    Steve

  7. #7
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks Steve for your help.

    Peter

Posting Permissions

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