Last 6 matches home and away soccer form table

  • Re: Last 6 matches home and away soccer form table


    Here is another option where you can use whole column references like D:D so you don't have to limit the ranges (in the case that they move around).


    If you put the country in column A just before their series to identify the series, e.g. type Italy (or Italia) in A1, England (or Inghilterra) in A2309 (just before the England games)


    Then you can use this formula in Z3:


    =CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;K:K;">0")


    This formula looks for "Italy" in column A, then takes the number in column B just after the work (it knows now this is the last round of the series) and subtracts 5 from that, so it knows the last 6 rounds... and it calculates from there. Now you don't have to adjust ranges, just change the text "Italy" when you apply formula somewhere else.


    Hopefully that might help.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    Thanks dear,


    I understood.
    You simple the BEST.


    By rounds counting in B we can set countif formulas, about last six occurences, basing them on :


    > =MAX (B$1:B$100) - 6


    Now how to sum the scored goals in K & L ??? To calculate under over 1.5; 2.5; 3.5 and how calculate both teams score and not ???


    Thanks so much.


    Simple a Genius.


    Regards

  • Re: Last 6 matches home and away soccer form table



    Another big solution.


    What a king you are!!


    You are doping me.

  • Re: Last 6 matches home and away soccer form table


    Quote from FrankieBue;699251

    ...


    Now how to sum the scored goals in K & L ??? To calculate under over 1.5; 2.5; 3.5 and how calculate both teams score and not ???


    ...


    Here, if you want to use the COUNTIFS approach, we will need to use the helper columns that you have already established in column Q to T (otherwise we will need to use SUMPRODUCT.. which is like the array formula and is not efficient).


    In AB3 the formula would be:


    =CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;Q:Q;AB$2)


    and in AC3:


    =CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;Q:Q;AC$2)


    both copied down.


    Similar formulas in AD:AH... just changing the last 2 parameters...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    Yes this last is an option that i appreciate;


    If i don't use ab$2 and ac$2... and i abandon the sumproduct array idea...


    Are there other ways???


    There is k$3; k$100;"> 0" to calculate if score


    I thought about ((k$1:k$100)+(L$1:L$100));"<=1" to calculate under 1,5...
    But i fall in error...


    I would clear NOQRST columns


    Thanks 4 your time Boss.


    Take care

  • Re: Last 6 matches home and away soccer form table


    "((k$1:k$100)+(L$1:L$100));"<=1" " ... Can't use that with COUNTIFS.. would need SUMPRODUCT....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    What to say Boss... you satisfy me on all my answers, doubts and curiosites.


    I have to leave and use the firsts above arrays formulas...


    " because of in A3735 there's a 42nd adavanced round played game....


    In A3722 there's 22nd round replayed games... covering last six occurrences. "


    The written orders let me in error... as soon as for many leagues, english expecially.


    Well i ll put the last formulas you gave me... where i can.


    I don t know how to thanks for all your time you spent about my unknownledges.


    See you BOSS!!! Great teacher.


    You was the SOLUTION!!!


    Nice to meet you.
    Take care 4eva.


    Kind regards.

  • Re: Last 6 matches home and away soccer form table


    Please If you can i have the last true request,


    how can establish in near columns, for each team in y:y,
    their last sequences, in their last six total occurences, by not ordering them about home or away games???


    Last sequences about:


    win draw lost games


    U1 O1 games
    U2 O2 games
    U3 O3 games
    BTTS ... NO BTTS games


    i'm off topic... i'll search for by posting a new thread.


    Thanks 4 all BOSS
    Luciano.

  • Re: Last 6 matches home and away soccer form table


    Again, I am lost and don't quite understand your request. If you are trying to use different colours for each team, then it won't be possible using basic Excel functions. You will need a macro (VBA), which is not my expertise. You will need to repost the question in a new thread and wait for a VBA expert to respond.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    HI DEAR,


    i establish this formula, but i didn't understand


    Quote

    =CONTA.PIÙ.SE(B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3;K:K;">0")


    Quote

    This formula looks for "Italy" in column A, then takes the number in column B just after the work (it knows now this is the last round of the series) and subtracts 5 from that, so it knows the last 6 rounds... and it calculates from there. Now you don't have to adjust ranges, just change the text "Italy" when you apply formula somewhere else.



    By trying this i fall in a calculation tha returns me " 0 " ???

    Quote


    B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;


    i didn't understand " index" .... what i might to do???


    thank's for all, as soon as for an helpfully reply.



    take care.
    luciano

  • Re: Last 6 matches home and away soccer form table


    INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5


    This is an Index/Match formula.


    If you enter "Italy" (without quotes) in A1, CONFRONTA("Italy";A:A;0) finds Italy in column and returns the position (i.e row number). I add 2 (this number will depend on where you type "Italy" and how far below the first row of scores shows) to that to get to the first row showing scrores for series 19. Then INDICE(B:B;CONFRONTA("Italy";A:A;0)+2) returns the number that is in column B at the point where the MATCH()+2 identifies. In this case the result is 19. The -5 subtracts 5 to give 14. So now the COUNTIFS() will count how many rows in column B are larger than or equal to 14 at the same time as column D is equal to the team identified in Y3 and at the same time also as column K is greater than 0.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    hi dear,


    tx so much for this other helpfull info.



    i established all my needed functions about your big golden infos...


    i'll let me to set some functions in vba code, to resize the file at the minimum!!!


    please, can i ask something about vba codes in these days???


    what do you suggest me about ???
    i want to obtain only calculated values, by clearing formulas and functions inserted in any cells, can i try a method to keep formulas and translate them in vba...??? are there any functions???




    if you can't... i'll move to post it by opening a new thread.
    thank's for all you have done for me.


    take care.
    your,
    regards.

  • Re: Last 6 matches home and away soccer form table


    Try posting a new thread. VBA is not my forte.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    hi dear i'm alive :)


    how can i set your formula as written down with MATR.SOMMA.PRODOTTO???
    for the last six occurrences starting from the top, the beginning rows???


    ... like wrote by you some day ago...


    =SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);1))))



    tx of all.


    take care.


    frank

  • Re: Last 6 matches home and away soccer form table


    May I ask why?


    It will still be an Array formula... and so the efficiency won't necessarily be better.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    Hi dear txx.
    Chiaro.


    Please,
    How can i sum the scored goals (in k:k) of a home team (d:d) for the last six occurrences ???


    Tx 4 your time.
    Take care

  • Re: Last 6 matches home and away soccer form table


    Are you using my last suggestion about entering "Italy" in A1 so you can use formula: =COUNTIFS(B:B,">="&INDEX(B:B,MATCH("Italy",A:A,0)+2)-5,D:D,Y3,K:K,">0")


    If yes, we can use similar for summing.


    =SUMIFS(K:K,B:B,">="&INDEX(B:B,MATCH("Italy",A:A,0)+2)-5,D:D,Y3)


    or


    =SOMMA.PIÙ.SE(K:K;B:B;">="&INDICE(B:B;CONFRONTA("Italy";A:A;0)+2)-5;D:D;Y3)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Last 6 matches home and away soccer form table


    =SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);1))))


    Hi dear,
    Tx so much, i m using this one for english conference.


    Tx for ur support

  • Re: Last 6 matches home and away soccer form table


    Just replace the 1 at the end with the K range.


    =SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)<=PICCOLO(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);RIF.RIGA($D$3:$D$870));6);SE(($D$3:$D$870=Y3)*($K$3:$K$870>0);[COLOR="#FF0000"]$K$3:$K$870[/COLOR]))))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!