Posts by 1003064

If I enter a new game it doesnt update? Is this an easy fix.

can you split the cell so it contains the 3 form and 6 form separately like in the other poster solution

Thanks ZarinaSA. The formula works great.

KjBox has been on this so he is still the winner of the contest but your formula will be really useful.

Thanks KjBox, how did you get on with PayPal.

I think the other soultion works for me being in a cell.

I will be entering it manually, I have lots of different datasets that I want to use this form, is it relatively straightforward to transfer this to other sheets.

Its not even really filtering, it would just be really useful if the form was in a cells rather than a message box.

Is that possible?

No sorry

The why it works now is great

But I would really like that information in cells,

For example to the right of the data that is necessary it to gove me that information in a cell rather than a message box

Then I could filter one team out, such as Liverpool, and I would be able to see Liverpools form across the season.

Is this possible

Thanks again.

Thanks,

I get the #NUM! error message, what am I doing wrong?

Ok KjBox,

However, is there a way to extract the information into cells. I ideally would like them in cells so I can filter the sheet.

Also will it continue to work when I add more data in.

You're an expert.

Thanks for your help!

Sam

Ok say I have multiple sales men and every time they post a sale I write it in a list.

So for example,
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 67, align: right"]1[/TD]
[TD="width: 67, align: right"]21[/TD]
[TD="width: 67"]John[/TD]

[/tr]

[tr]

[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]

[td]

John

[/td]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]

[td]

Jack

[/td]

[/tr]

[tr]

[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]

[td]

Paul

[/td]

[/tr]

[tr]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]

[td]

John

[/td]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]40[/TD]

[td]

John

[/td]

[/tr]

[/TABLE]

I want to know the average of Johns last three sales at t = 6, which is 26.66668
I also want to know the average of Johns last three at t = 5, which is 20.33333

I want to be able to drag the formula down.

Thank you in advance.

Sam

Any ideas KjBox I still havent sort this.

Hi KjBox,

I have been making some progress with my Excel Sheet.

I have been able to find the form for the last X games at home (I have done three for now) and I could replicate the table for the away form.

I will attach the sheet so you get an idea of how I want to use it going forward.

My problem is, this method would find the last three games away, and the last three games at home. Whilst this information is useful for me, I really need the form for the last three games, this could be at Home Home Away, or Away Home Home and the other alternatives as an example.

"Do you enter upcoming matches (less the scores, obviously) and want the previous 6 (3) results for each team to display, or do you enter everything only after matches have been played and the current match plus previous 5 (2) results for each team to display?" - Yes exactly!

I really appreciate you getting back to me.

Thanks

Sam

[ATTACH]n1206416[/ATTACH]

Hi Guys,
I have a spreadsheet with lots of football scores, I have attached it so you know what I'm talking about.

Here is one of the entries, [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TR="class: cke_show_border"]
[TD="width: 67"]Ref[/TD]
[TD="width: 67"]Output[/TD]
[TD="width: 89"]Home[/TD]
[TD="width: 89"]Away[/TD]
[TD="width: 67"]HTHome[/TD]
[TD="width: 67"]HTAway[/TD]
[TD="width: 77"]Date[/TD]
[TD="width: 67"]FTHome[/TD]
[TD="width: 67"]FTAway[/TD]
[TD="width: 67"]HT[/TD]
[TD="width: 67"]FT[/TD]
[TD="width: 67"]HT FT[/TD]
[TD="width: 67"]Away HT[/TD]
[TD="width: 67"]Away FT[/TD]
[TD="width: 75"]Away HT FT[/TD]
[/TR]
[TR="class: cke_show_border"]

[td]

1

[/td]

[td]

1

[/td]

[td]

Arsenal

[/td]

[td]

Leicester

[/td]

[td]

2

[/td]

[td]

2

[/td]

[td]

11/08/2017

[/td]

[td]

4

[/td]

[td]

3

[/td]

[td]

D

[/td]

[td]

W

[/td]

[td]

DW

[/td]

[td]

D

[/td]

[td]

L

[/td]

[td]

DL

[/td]

[/TR]
[/TABLE]
So from this, I know that Arsenal Won the Game 4-3.

I have a long list of results like this,

What I want to do, is known the previous form before a game, so for example, on Boxing Day, Liverpool Played Swansea, [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TR="class: cke_show_border"]
[TD="width: 67"]Ref[/TD]
[TD="width: 67"]Output[/TD]
[TD="width: 89"]Home[/TD]
[TD="width: 89"]Away[/TD]
[TD="width: 67"]HTHome[/TD]
[TD="width: 67"]HTAway[/TD]
[TD="width: 77"]Date[/TD]
[TD="width: 67"]FTHome[/TD]
[TD="width: 67"]FTAway[/TD]
[TD="width: 67"]HT[/TD]
[TD="width: 67"]FT[/TD]
[TD="width: 67"]HT FT[/TD]
[TD="width: 67"]Away HT[/TD]
[TD="width: 67"]Away FT[/TD]
[TD="width: 75"]Away HT FT[/TD]
[/TR]
[TR="class: cke_show_border"]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/TR]
[TR="class: cke_show_border"]

[td]

194

[/td]

[td]

20

[/td]

[td]

Liverpool

[/td]

[td]

Swansea

[/td]

[td]

1

[/td]

[td]

0

[/td]

[td]

26/12/2017

[/td]

[td]

5

[/td]

[td]

0

[/td]

[td]

W

[/td]

[td]

W

[/td]

[td]

WW

[/td]

[td]

L

[/td]

[td]

L

[/td]

[td]

LL

[/td]

[/TR]
[/TABLE]

I would like to know the form of Liverpool and Swansea in the last 6 games, for example, was Liverpools form W W W W W W, did they win all the last six games.

I will eventually be putting this information in number form, i.e. (3 for a win, 1 for a Draw, 0 for a Loss), so for example, a score of 18 for liverpool would mean that they won the last three.

The games can be home and away, I want to know the form of the last 6 games, regardless of whether it was home or away.

I also want to be able to drag the formula down, so then I can enter next weeks fixtures and get the form data based on the past form.

I have seen lots of people make a form table, the different with mine is I want to know them form at each fixture throughout the season.

It would be handy if the formulas could easily be adapted to check the form of the last 3 games also - if it is easy! [ATTACH]n1206384[/ATTACH]

Any questions let me know.

Cheers!

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]

Hi Guys,

I am looking to calculate the form for a team in my sheet based on the last 4 fixtures.

For example, for Liverpool playing on Boxing day, I want a new excel column telling me the last 4 results, i.e. W,W,L,D, this can come in numbers, i.e. a 3 for win, 0 for loss, 1 for a draw, if I a had a number say 7, I would know that their form is W W L D.

I can dothisusing simple formulas if I split the, teams, individually, i.e. a section of Liverpool, a section for Chelsea, but I would like to be able to do it for the entire sheet quickly as I have a few sheets I want to do it for.

I have uploaded the sheet.

Any help would be appreciated.

Best

Sam

I am struggling to upload the file but its at this url called Premier League http://www.football-data.co.uk/mmz4281/1718/E0.csv