Last 6 matches home and away soccer form table

    http://wikisend.com/download/3…ope%20You%20solve....xlsm


    Hi all mates;
    I hope you'll help me.
    I want learn how i can count only the lasts six matches of a football Team "X".
    It will play 4 matches in home and 2 away; it had played 3 matches in home and 3 away or it could have played 1 match in home and 5 away... i will want count always and only its six lasts matches to calculate some statistics.


    [Blocked Image: http://i42.tinypic.com/346l4xs.jpg]



    Kind regards.
    See You

    Re: Last 6 matches home and away soccer form table


    Try these formulas:


    To get count of scores over 0


    =SUM(IF($D$3:$D$870=Y3,IF(ROW($D$3:$D$870)>=LARGE(IF($D$3:$D$870=Y3,ROW($D$3:$D$870)),6),IF($K$3:$K$870>0,1))))


    Note: After you enter the formula in the cell, hold the CTRL key and the SHIFT key together, then press ENTER. You should see { } brackets appear around the formula, then you can copy formula down the column


    To get count of no scores:


    =SUM(IF($D$3:$D$870=Y3,IF(ROW($D$3:$D$870)>=LARGE(IF($D$3:$D$870=Y3,ROW($D$3:$D$870)),6),IF($K$3:$K$870=0,1))))


    with same way of entering the formula as above.


    or even simply =6-Z3 should give same result.


    IF you have ITALIAN version of EXCEL, you could use these formulas:


    =SOMMA(SE($D$3:$D$870=Y3,SE(RIF.RIGA($D$3:$D$870)>=GRANDE(SE($D$3:$D$870=Y3,RIF.RIGA($D$3:$D$870)),6),SE($K$3:$K$870>=0,1))))


    and


    =SOMMA(SE($D$3:$D$870=Y3,SE(RIF.RIGA($D$3:$D$870)>=GRANDE(SE($D$3:$D$870=Y3,RIF.RIGA($D$3:$D$870)),6),SE($K$3:$K$870=0,1))))


    and if you have semi colon separators, instead of commas:


    =SOMMA(SE($D$3:$D$870=Y3;SE(RIF.RIGA($D$3:$D$870)>=GRANDE(SE($D$3:$D$870=Y3;RIF.RIGA($D$3:$D$870));6);SE($K$3:$K$870>=0;1))))


    and


    =SOMMA(SE($D$3:$D$870=Y3;SE(RIF.RIGA($D$3:$D$870)>=GRANDE(SE($D$3:$D$870=Y3;RIF.RIGA($D$3:$D$870));6);SE($K$3:$K$870=0;1))))


    NB: My range is up to row 870 to cover all Italian teams.... you can extend it further, but with these special Array formulas, you should not cover more than is necessary or it will be less efficient.

    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


    hello mate.


    thanks so much. you are the best!!! i don't know how to thanksgive you!! :)


    Now i have a question about some mistakes by counting statistics... perhaps i apologized something:


    for example by taking "INTER" in home... if i count Inter last games played in home to calculate "scored Yes", with your formulas, i'll see displayed it with three games in home, but inter played, in last six total games, 4 times in home and two games by away... how to correct and solve this mistake???


    i want its last six games in order of the decrescending rounds played... week by week...


    I want learn how i can count only the lasts six matches of a football Team "X".
    It will play 4 matches in home and 2 away; it had played 3 matches in home and 3 away or it could have played 1 match in home and 5 away... i will want count always and only its six lasts matches to calculate some statistics.


    thanks dear,


    i'll wait for your time.
    thanks of all.


    take care,
    regards.


    luciano

    Re: Last 6 matches home and away soccer form table


    Ok, I thought you wanted to count their last 6 HOME games....


    Try these formulas:


    =SUM(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),IF(ROW($D$3:$D$870)>=LARGE(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),ROW($D$3:$D$870)),6),IF($K$3:$K$870>0,1))))


    =SUM(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),IF(ROW($D$3:$D$870)>=LARGE(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),ROW($D$3:$D$870)),6),IF($K$3:$K$870=0,1))))


    remember to confirm with CTRL+SHIFT+ENTER and copy down

    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


    No dear.


    If you see Inter team, in d:d, in last six serie A rounds, inter played 4 games like home and 2 by away.


    Otherwise i don't know what and how to expalain the last six games in last six rounds i want how to calculate statistics in last home and away games il last six rounds.


    I hope you'll understand me.


    Thanks for your time.


    Take care
    Regards

    Re: Last 6 matches home and away soccer form table


    I thought about that just now and was about to post a correction.... you beat me....


    try these:
    =SUM(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),IF(ROW($D$3:$D$870)>=LARGE(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),ROW($D$3:$D$870)),6),IF(($D$3:$D$870=Y3)*($K$3:$K$870>0)+($F$3:$F$870=Y3)*($L$3:$L$870>0),1))))


    and
    =SUM(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),IF(ROW($D$3:$D$870)>=LARGE(IF(($D$3:$D$870=Y3)+($F$3:$F$870=Y3),ROW($D$3:$D$870)),6),IF(($D$3:$D$870=Y3)*($K$3:$K$870=0)+($F$3:$F$870=Y3)*($L$3:$L$870=0),1))))


    Note: I resimplified the formulas....

    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


    In Italian the last formulas are:


    =SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)>=GRANDE(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)+($F$3:$F$870=Y3)*($L$3:$L$870>0);1))))


    and


    =SOMMA(SE(($D$3:$D$870=Y3)+($F$3:$F$870=Y3);SE(RIF.RIGA($D$3:$D$870)>=GRANDE(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)+($F$3:$F$870=Y3)*($L$3:$L$870=0);1))))


    In your screen shot, I can see that you did not follow my instruction. After you enter the formula, hold the CTRL key and SHIFT keys down, then press ENTER. You will see these bracket { } around the formula. Then you can copy formula down.

    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



    dear last two kinds of formulas give me different results how you can calculate on attacked url file.


    The first is good to calculate last six home+away.


    Can t we solve lasts my question ???


    Have a nice dinner.


    See you
    Take care.
    Luciano

    Re: Last 6 matches home and away soccer form table


    Have a look here: https://www.dropbox.com/s/yyv2…e%20You%20solve...-1.xlsm


    (note: I removed your first sheet to reduce size, but still too big to upload to Ozgrid)....


    I only completed column Z:AA... you can fill in the rest with same idea.

    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 nbvc,



    In D column We have Home Teams ------- In F column We have Away Teams!!!
    So there are many rows which contain Home and Away Teams… on the right of these,
    in Q, R, S, T columns, there are some calculations about their results:
    “ score or not, under over goals, both teams score… “

    I want count in last six occurences, about last six league rounds, like shown in column B, as follows:

    How many times a Home Team has scored or not ( column N) … so this for other Q, R, S, T
    How many times an Away Team has scored or not ( column O) … so this for other Q, R, S, T

    !!!!!!!!!!!!!!! All this about ONLY LAST SIX LEAGUE ROUNDS !!!!!!!!!!!!!!!!!!!!

    For example in Italy Serie A league championship, like in column B,
    from the first last round, the 19th, until the sixth last round, the 14th round…
    by covering and taking always the last six occurences…
    Z9: Inter played _4 games in home_ by scoring 4 times, like shown in Z:Z … ( 4 scored Yes)
    Z9: Inter played _2 games away_ by scoring only 1 time and not scoring also 1 time ( 1 scored Yes & 1 Not)

    By following these rules I want calculate what I explained, about all the observable league teams in Y:Y

    Note Well:
    In other leagues, expecially English leagues, when I refresh query web about new league championship results, I find postponed or abandoned games of previous league rounds which are replayed.
    These replayed games are assorted, mixed to very last 6 played rounds…
    According to my ideas They are calculated like lasts by me… and sometimes, by counting last six occurences of a Team, covering last six league rounds, we should find a Team which played also 5 times like Home and only 1 Away.

    Hope I’ll be able to be helped,
    I’M STUCK.

    TAKE CARE.
    Regards.
    Luciano


    [Blocked Image: http://i44.tinypic.com/oggm4i.jpg]
    http://i44.tinypic.com/oggm4i.jpg


    http://wikisend.com/download/3…ope%20You%20solve....xlsm

    Re: Last 6 matches home and away soccer form table


    Let's try this again....


    In Z2:


    =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))))


    in AA2:


    =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))))


    in AK2:


    =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(($F$3:$F$870=Y3)*($L$3:$L$870>0);1))))


    in AL2:


    =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(($F$3:$F$870=Y3)*($L$3:$L$870=0);1))))

    each formula must be confirmed with CTRL+SHIFT+ENTER keys, so that { } appear around the formulas:

    then you can copy each down.

    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




    You are a BOMBBBBBBBBBBBBBBBBBBBBBBBBBB!!!!!!!!!
    SOLVED
    SOLVED
    SOLVED
    SOLVED


    so now i let me to try the other statistics about columns q r s t ... by taking your formulas!!!


    nice to meet you deaaar


    muy feliz.


    kind regardsss

    Re: Last 6 matches home and away soccer form table



    Hello my dear friend,
    making even thanks to you for what You have suggested to me,


    I have to ask:
    deleting the calculations in columns NOQRST. ..
    to lighten the sheet ...
    because these calculations appear to me already in the cells of calculation from Z to CL ...


    Can I calculate formulas without my calculations in the form of matrices??


    I could help with the cells help? What do you suggest?
    Could you give me more advices or straights???


    Thank you for everything you have done and will do ...
    look forward to hearing from you...


    take care;
    regards,
    luciano.

    Re: Last 6 matches home and away soccer form table


    In fact, if you can set fixed ranges to cover only those last 6 rounds e.g. rows 3 to 67 only, then you can simplify with COUNTIFS


    In Z3 the formula (in Italian) would now simply be:


    =CONTA.PIÙ.SE($D$3:$D$67;Y3;$K$3:$K$67;">0")


    In AA3


    =CONTA.PIÙ.SE($D$3:$D$67;Y3;$K$3:$K$67;"0")


    in AK3


    =CONTA.PIÙ.SE($F$3:$F$67;Y3;$L$3:$L$67;">0")


    and in AL3:



    =CONTA.PIÙ.SE($F$3:$F$67;Y3;$L$3:$L$67;"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



    Dear friend, about ONLY LAST SIX LEAGUE ROUNDS...


    For example in Italy Serie A league championship, like in column B,from the first last round, the 19th, until the sixth last round, the 14th round…by covering and taking always the last six occurences…Z9: Inter played _4 games in home_ by scoring 4 times, like shown in Z:Z … ( 4 scored Yes)


    Z9: Inter played _2 games away_ by scoring only 1 time and not scoring also 1 time ( 1 scored Yes & 1 Not)By following these rules I want calculate what I explained, about all the observable league teams in Y:Y


    Note Well:In other leagues, expecially English leagues, when I refresh query web about new league championship results, I find postponed or abandoned games of previous league rounds which are replayed.These replayed games are assorted, mixed to very last 6 played rounds…


    According to my ideas They are calculated like lasts by me… and sometimes, by counting last six occurences of a Team, covering last six league rounds, we should find a Team which played also 5 times like Home and only 1 Away.


    As follows, see in A5027... ENGLISH CONFERENCE...
    THERE ARE THREE REPLAYED GAMES THAT I COUNT LIKE LAST SIX OCCURENCES...
    SO RAWS CHANGES... AND I MISTAKE CALCULATION... WITH HIDE RAWS..


    How can count these as follows... with countif???



    In Z2:=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))))


    in AA2:=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))))


    In AK2:=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(($F$3:$F$870=Y3)*($L$3:$L$870>0);1))))


    in AL2:=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(($F$3:$F$870=Y3)*($L$3:$L$870=0);1))))



    Thanks 4 all u ll do.
    Take care.


    Regards

    Re: Last 6 matches home and away soccer form table


    I don't understand anything you said here.


    Are you saying the COUNTIFS formulas I just gave won't work? Are the original formulas that you quote above working or not?! I don't understand your new question!

    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


    Dear,


    the original formulas, under form of arrays, that you quoted above are working, so now i would translate them in simple formulas without under form of arrays.


    Exscusme for the mutiple posts, i believe in you and i'll follow only you.


    I hope you understand.


    Nice to wait for You.


    Regards

    Re: Last 6 matches home and away soccer form table


    Since you are limiting to only "last 6" out of many more, then it is difficult to not involve the array formulas. You might be able to use Helper columns, but those formulas may either become a little complicated, or you will need multiple helper columns.


    The best way to simplify the formula is to use the defined range that only covers the last 6 rounds of each countries teams... (for example again, for Italy, that would be D3:D67). Then you can use the simple COUNTIFS formulas I suggested.


    Maybe if you can use column B to determine last 6 (ie. check if round number is less than (19-5... or 14)


    e.g. In Z3:


    =CONTA.PIÙ.SE($B$3:$B$870;>="&$B$3-5;$D$3:$D$870;Y3;$K$3:$K$870;">0")

    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!

Register Yourself Login