Posts by FrankieBue

    Hello everyone,

    I will be patient and very grateful to who will assist me to solve my problem.

    With the following code, I jump absent results (those which are "-").
    I get the TOTAL of goals scored and had, so also the TOTAL victories, defeats or draws of the LAST 6 GAMES of a team.

    How do I get the results individually (goals scored and had, victory, draw and/or defeat) SINGLE DAY for single day, always in the LAST SIX games played and always JUMPING THE RESULTS eguals to "-" ?

    Who will assist me can try and make me appear in order the results, goals, wins, draws and defeats ....
    in order, day to day, in their total 6 last games?
    Thank's so much to everyone who will read me.

    I await for You dears.


    I get stuck in the routine...

    here i am and i write as follow:


    if you open the file it doesn't crash cause macros are disabled so you can open vba with alt + f11

    Sheet Elaborazioni - Sheet 1

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = Range("E1").Column Or Target.Column = Range("F1").Column Then
    End If
    End Sub


    Private Sub UserForm_Terminate()
    ActiveCell.Value = ComboBox1.Value
    End Sub

    My woorkbook an all the others sheet: " Archivio - Studies - Spiegazione " have

    Option explicit

    Modulo 1 where i have several functions... for example:

    This is it all dear.

    i hope you will find a solution.

    see u.

    thank's for all your time.

    kind regards.


    Re: I get stuck in the routine...

    Hi dear PIKE,
    Thank you for your interest and your response.

    In practice:

    When I open the worksheet ...
    ... in Elaborazioni sheet...
    I click on G4 to select a choice of game and the CPU goes into overload and crashes me running and everything ... this data validation in G:G is linked to N:N e O:O rules;

    I wrote a series of combinatorial code ... If you open Vba you will find them in MODULE 1
    these are invoked in P:P e Q:Q... in ELABORAZIONI sheet...

    I don't understand the problem ... will be a userform problem???
    There will be a way to lighten these Combinatorial processes?

    Thank you for your cooperation and for all the time that You give me.

    Yours sincerely,

    Re: Last 6 matches home and away soccer form table


    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

    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...


    tx of all.

    take care.


    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.

    Re: Last 6 matches home and away soccer form table

    HI DEAR,

    i establish this formula, but i didn't understand




    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 " ???



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

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

    take care.

    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

    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

    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

    Another big solution.

    What a king you are!!

    You are doping me.

    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.


    Re: Last 6 matches home and away soccer form table


    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.


    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...

    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.


    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;

    Re: Last 6 matches home and away soccer form table


    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