+ Reply to Thread
Results 1 to 15 of 15

Count last 3 results only

Hybrid View

junada0 Count last 3 results only 04-21-2013, 10:58 AM
bebo021999 Re: Count last 3 results only 04-21-2013, 11:50 AM
junada0 Re: Count last 3 results only 04-21-2013, 12:07 PM
MarvinP Re: Count last 3 results only 04-21-2013, 12:01 PM
Lemice Re: Count last 3 results only 04-21-2013, 12:05 PM
Lemice Re: Count last 3 results only 04-21-2013, 12:14 PM
MarvinP Re: Count last 3 results only 04-21-2013, 12:19 PM
martindwilson Re: Count last 3 results only 04-21-2013, 12:16 PM
Lemice Re: Count last 3 results only 04-21-2013, 12:23 PM
martindwilson Re: Count last 3 results only 04-21-2013, 12:27 PM
junada0 Re: Count last 3 results only 04-21-2013, 01:57 PM
bebo021999 Re: Count last 3 results only 04-21-2013, 02:07 PM
junada0 Re: Count last 3 results only 04-22-2013, 01:50 AM
bebo021999 Re: Count last 3 results only 04-22-2013, 02:40 AM
junada0 Re: Count last 3 results only 04-23-2013, 02:07 AM
  1. #1
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Count last 3 results only

    I have a table of results and would like to check the last 3 known positions across a row of data excluding a few columns and provide an answer on the results if finishing in 1st or 2nd place.

    Sample spreadsheet enclosed

    Answer / formula required in column "Q"
    Look for the last 3 results in each row counting backwards and excluding columns "H" and "P"
    and if either a 1st or 2nd place then show "0" any result which is 3 or higher in last 3 results then show "1"

    Thanks in anticipation.

    Junada0
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Count last 3 results only

    Let see row #5, the last 3 results finished at 9th position, why it is showed "0"?
    Quang PT

  3. #3
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Count last 3 results only

    Hi,

    Each row from #3 to #8 are player results, not sure how you read 9th position

    The last 3 results in row#5 are 3,5 and 2 so answer should be "0" as a second place was scored in the players last 3 results.

    If any player scores a 1st or 2nd place in the last 3 results then answer should be "1"

    Hope that helps.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,227

    Re: Count last 3 results only

    Hi Juanada

    In Q3 put this formula and pull down.

    =IF(LEN(SUBSTITUTE(SUBSTITUTE(RIGHT(A3&B3&C3&D3&E3&F3&G3&I3&J3&K3&L3&M3&N3&O3,3),1,""),2,""))=3,1,0)
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Count last 3 results only

    Hello,

    I managed to come up with an Array formula which is painfully long and not elegant at all ...
    =--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),1))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),2))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),3))>=3)
    I have also attached your sample file with it

    Edit: To avoid having #NUM! error when you have less than 3 values, use this one instead
    =IFERROR(--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),1))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),2))>=3)*--(INDEX($A3:$O3,LARGE(IF(NOT(ISBLANK($A3:$O3))*(COLUMN($A3:$O3)<>8),COLUMN($A3:$O3)),3))>=3),0)
    Thus it will always be 0 when there are less than 3 numbers to check.
    This one also works in all other cases that the formula above can.
    Attached Files Attached Files
    Last edited by Lemice; 04-21-2013 at 12:35 PM.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  6. #6
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Count last 3 results only

    Marvin, I really really like your solution, it was a good one.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,227

    Re: Count last 3 results only

    Thanks - It will only work if there are single digit numbers in the cells. Because the possible places are 1 to 8 then it should work. I looked at possible formulas of Lookup, Index and Array and all seemed to hard. I went with a string/text function because the Right last 3 characters seemed to work for this problem. I wonder what happens if there are only two numbers or perhaps no numbers? Will my formula or yours still work?

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count last 3 results only

    a longish non array aproach
    Formula: copy to clipboard
    =IF(AND(INDEX(A3:O3,LARGE(INDEX((A3:O3<>"")*(MOD(COLUMN(A3:O3),8)<>0)*COLUMN(A3:O3),0),1))>2,INDEX(A3:O3,LARGE(INDEX((A3:O3<>"")*(MOD(COLUMN(A3:O3),8)<>0)*COLUMN(A3:O3),0),2))>2,INDEX(A3:O3,LARGE(INDEX((A3:O3<>"")*(MOD(COLUMN(A3:O3),8)<>0)*COLUMN(A3:O3),0),3))>2),1,0)

    marvinp
    =IF(LEN(SUBSTITUTE(SUBSTITUTE(RIGHT(A3&B3&C3&D3&E3&F3&G3&I3&J3&K3&L3&M3&N3&O3,3),1,""),2,""))=3,1,0) falls over if last 3 scores are say 11,6,7
    Last edited by martindwilson; 04-21-2013 at 12:20 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Count last 3 results only

    My formula will work as long as all of the values are number, and there are more than 2 (I should put an IFERROR in front of it).
    But really, I like the logic in your formula, really simple and clear!

  10. #10
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count last 3 results only

    maybe just
    nope not that

  11. #11
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Count last 3 results only

    Hii Guys,

    Really appreciate all your help, the array solution really works for me as some of the results could be higher than 8, I did not diplay any higher results as in trying to keep the question simple, sorry for any confusion and again many thanks.

    Junada0

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Count last 3 results only

    Hi guys,
    Alternative array option may be shorter alot:
    =(SUM(1*(LOOKUP(LARGE(IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0)*($A3:$O3>0),{1,2,3}),$A$2:$O$2,A3:O3)>2))=3)*1

  13. #13
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Count last 3 results only

    Hi bebo,

    I copied your solution to cell Q3 but got an error "#value!", not sure where I went wrong, would also like to understand how the formula works in case i need to change any values when I copy to anothe sheet with different cell positions and maybe extra columns etc.

    Junada0

  14. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Count last 3 results only

    This is an array formula and must be confirmed by Ctrl-Shift-Enter.
    Sharing my idea of bulding formula:
    1) IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0): ={1,2,3,4,5,6,7,0,8,9,10,11,12,13,14}: returns weekdays which showed in row #2, where "count" says 0
    2) (a) IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0)*($A3:$O3>0):{1,2,3,0,0,0,0,0,8,9,10,11,12,0,14}: returns weekdays where results of line #3 >0
    3) LARGE (a,{1,2,3}):{14,12,11}: returns 3 largest weekdays (from the right, offcourse)
    4)b) LOOKUP(LARGE (a,{1,2,3}),$A$2:$O$2,A3:O3)={7,6,8}: returns 3 scores (of 3 largest weekdays)
    5)LOOKUP(LARGE (a,{1,2,3}),$A$2:$O$2,A3:O3)>2 = {1,1,1}. If all>2 (=all=1), SUM =3=> results 1
    The final:
    =(SUM(1*(LOOKUP(LARGE(IF(ISNUMBER($A$2:$O$2),$A$2:$O$2,0)*($A3:$O3>0),{1,2,3}),$A$2:$O$2,A3:O3)>2))=3)*1

    Hope this helps!

  15. #15
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Microsoft 365 Version 2404
    Posts
    118

    Re: Count last 3 results only

    Great, thanks for the explanation, this helps me understand more.

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1