Dragging down a formula based on ranges

  • Hi Guys,


    I have got this excel spreadsheet called 'ToUpload' which I will attach.


    I am calculating the form of football teams based on ranges. As you can see, the formulas at the present tell me the last three results of the team, how many wins and losses.


    My question is, when I add more data down, how can it calculate the last three again, so I can almost drag the formula down and get the previous game for every form.


    To be as clear as possible


    If the last three games were all draws, the current formula tells me 3 D's,


    If the next game is a Loss, I want to be able to drag down the formula and it tell me the three most recent, i.e. 1 L and 2 D.


    I hope someone can help.


    Here is a link that may be useful, this is where I got the formula from, but they are trying to create a form table, I want to know the most recent form at every fixture - https://www.mrexcel.com/forum/…-football-form-table.html


    Thanks in advance.


    Sam
    [ATTACH]n1206361[/ATTACH]

    Files

    • ToUpload.xlsx

      (20.66 kB, downloaded 72 times, last: )