Combine sets of football results formula

[roversfan09]

Hi guys

Just wondering if someone can help me speed up a process.

In the attached sheet you will see some sets of soccer results which I type in as text on sheet 1. What I want to do is combine the sets of results from both teams in order to find the most likely scoreline at full time,and also show the percentage alongside.

On sheet 2 is what I would like to achieve and you can see there how both sets of results have been combined for both teams on Sheet 1.

So if I could just type in the scores into sheet 1 and once typed,sheet 2 will auto combine the results from sheet 1 that would be great.

Any help would be much appreciated (and also speed up the process for me)

Thanks
Steve

[FlameRetired]

I had to do some rearranging of your Sheet2 to work out the formulas. I don’t have all the data collated
as you show on your sheet. That is the result of the combining formulas I used. I am hoping this won’t be
unduly awkward. Have a look and see what you think.
In columns E, F and H of Sheet2 are all variations of this formula I borrowed from Tony Valko. They
combine columns from Sheet1.

Please Login or Register  to view this content.

This formula sums the combined outcomes in column G.

Please Login or Register  to view this content.

In column I this formula gives the % Chance.

Please Login or Register  to view this content.

Not all of our figures agree exactly. We disagree by a few tenths of a %.

The workbook is attached. Let us know what you think.

[samba_ravi]

see the attached file

[FlameRetired]

Yeah, that's better. Nicely done, Siva.

[roversfan09]

Guys first off thank you both for taking the time to help me with my sheet,much appreciated

see the attached file

Siva your sheet is perfect for my needs,just one thing... To test it I have copied in another set of results,it was fine on the previous set I included in the sheet I uploaded. However it is possible that the list of results for either team may be ''longer'' ie the list of scores may go down to cell 16 in some cases for example.. I have reuploaded a sheet with a longer set of results for the away team,as you will see on sheet two it seems to be incomplete and also in cell A11 there is just a ''0'' in the cell instead of a ''most likely score''... Im sure its only a matter of tweaking a formula maybe,but the formulas you used are beyond my knowledge so Im not sure where to edit them..

If by any chance you could take a few mins to have a look when you get a chance,it would be much appreciated ( again )

[samba_ravi]

a2=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$100,MATCH(0,INDEX(COUNTIF($A1:$A$1,Sheet1!$A$2:$A$100),0),0),0),INDEX(Sheet1!$E$2:$E$100,MATCH(0,INDEX(COUNTIF($A1:$A$1,Sheet1!$E$2:$E$100),0),0),0)),"")

b2=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100+0))+SUMPRODUCT((Sheet1!$E$2:$E$100=$A2)*(Sheet1!$F$2:$F$100+0))

c2=TEXT((B2+0)/SUMPRODUCT((B$2:B$100+0)),"0.00%")

edit 100 as per your needs

[roversfan09]

Thats perfect Siva..all sorted... Thanks again for your help, I will just go ahead now and mark this as solved

Cheers Folks
Steve