creating a btts/wbtts table in excel

FONZY6149
Handicapper
Tips
Posts: 15
Joined: Mon Mar 24, 2008 12:00 am

creating a btts/wbtts table in excel

Postby FONZY6149 » Sun Feb 05, 2017 12:37 pm

hi
wondering if anyone on here could help please,i`ve been trying to create a table in excel to show percentages of matches with certain results.i copy and paste in a leagues results then in another worksheet i use the sumproduct function to find a certain team and calculate how many times the have w/d/l-this works ok,but i don`t know how to take this further by looking for a teams results that are wbtts,dbtts,lbtts.Is this possible with the sumproduct function??
Thanks in advance for any comments

Zipster
Stallion
Tips
Posts: 6224
Joined: Wed Apr 20, 2005 11:00 pm
Location: Ipswich, UK
Contact:

Re: creating a btts/wbtts table in excel

Postby Zipster » Sun Feb 05, 2017 3:00 pm

Hi Fonzy,

What is the formula that you are currently using for the one which work?

If you can copy & paste that in here then I may be able to help.

Or a simple description of how you have the results stored as in columns
OLBG Tipster Competition Assistant || A Great Man Once Said 'The first and the best victory is to conquer self' || Follow Me On Twitter

FONZY6149
Handicapper
Tips
Posts: 15
Joined: Mon Mar 24, 2008 12:00 am

Re: creating a btts/wbtts table in excel

Postby FONZY6149 » Sun Feb 05, 2017 9:57 pm

BTTS(+2.5) (-2.5) PLYD WIN LOSE WBTTS DBTTS LBTTS DRAW
Belen Alajuelense 1 2 Alajuelense
Cartagines Liberia 1 1 Belen 4 1 2 1
San Carlos Limon  0 0 4 2 Carmelita 4 0 3 1
Saprissa Santos DG 1 1 Cartagines
Carmelita Zeledon 0 1 Herediano
U.C.R. Herediano 1 0 Liberia
Limon  Carmelita   2 1 San Carlos
Zeledon U.C.R. 3 1 Santos DG
Santos DG Liberia  3 0 Saprissa
Herediano Saprissa 2 2 U.C.R.
San Carlos Belen 1 1 Zeledon
AlajuelenseCartagines  2 1
Liberia Herediano 2 2
Belen Santos DG 3 2
Carmelita San Carlos  2 3
Saprissa Limon 2 1
U.C.R. Cartagines 3 0
Zeledon Alajuelense 1 0
Cartagines Saprissa  3 0
Santos DG Zeledon 3 1
San Carlos  U.C.R. 3 1
Alajuelense Herediano  0 2
Belen Carmelita 1 3
Limon Liberia 4 1
Cartagines San Carlos 4 3
Herediano Santos DG  1 1
Liberia Alajuelense 1 0
Carmelita Saprissa 0 1
U.C.R. Limon 1 3
Zeledon Belen 2 1
Saprissa U.C.R. 1 0
Santos DG San Carlos 2 1
Belen Herediano  1 1
Carmelita Cartagines 2 2
Limon Alajuelense 2 2
Zeledon Liberia 1 1
Herediano Zeledon 2 0
San Carlos Saprissa  1 0
Liberia Belen  1 0
Cartagines Limon 3 1
U.C.R. Carmelita 0 0
AlajuelenseSantos DG 1 2

HI ZIPSTER
thanks for the response
i copy and paste in the results but because the scores are in a single cell i use text to columns to separate them into a cell each.the formula i use for the win column is =sumproduct ((b4:b45=l6)*(d4:d45>e4:e45)) b4:b45 being the home teams. =l6 matches them to carmelita d4:d45>e4:e45 where the goals are bigger in column d than column e as you can see this works with both teams.. but in the btts column the formula i have tried is =sumproduct((b4:b45=l6)*(d4:d45>0)*(e4:e45>0)) which as you can see returns 4 but should be 2
hope this is clear to understand

FONZY6149
Handicapper
Tips
Posts: 15
Joined: Mon Mar 24, 2008 12:00 am

Re: creating a btts/wbtts table in excel

Postby FONZY6149 » Sun Feb 05, 2017 10:00 pm

it was sorted all neat as i typed it in but when i posted it,it doesn`t look anything like how it appeared to me sorry hope you can understand it

Zipster
Stallion
Tips
Posts: 6224
Joined: Wed Apr 20, 2005 11:00 pm
Location: Ipswich, UK
Contact:

Re: creating a btts/wbtts table in excel

Postby Zipster » Mon Feb 06, 2017 10:14 am

I would personally ditch the SUMPRODUCT and go with COUNTIFS, it easier to work with and is much more quicker in the workbook.

Try something like this....

BTTS: =COUNTIFS(B:B,$I6,D:D,">0",E:E,">0")
PLAYED: =COUNTIF(B:B,$I6)
WIN: =COUNTIFS(B:B,$I6,D:D,">"&E:E)
LOSE: =COUNTIFS(B:B,$I6,D:D,"<"&E:E)
DRAW: =COUNTIFS(B:B,$I6,D:D,E:E)

WBTTS: =COUNTIFS(B:B,$I6,D:D,">0",E:E,">0",D:D,">"&E:E)
LBTTS: =COUNTIFS(B:B,$I6,D:D,">0",E:E,">0",D:D,"<"&E:E)
DBTTS: =COUNTIFS(B:B,$I6,D:D,">0",E:E,">0",D:D,E:E)

Obviously they only give you the numbers for either home or away, if you need the combined then you need to alter them slightly, for example the PLAYED would become =COUNTIF(B:B,$I6)+COUNTIF(C:C,$I6) so it adds the home to the away.

Basically the COUNTIFS allows multiple conditions so you have (Range1,Condition1,Range2,Condition2....) and so on, try putting those formulas in row 6 and then copy and paste the them down and see if they work, I have used the B:B, C:C & D:D because this means if you add new rows they will be automatically updated.

Let me know if it works.
OLBG Tipster Competition Assistant || A Great Man Once Said 'The first and the best victory is to conquer self' || Follow Me On Twitter

FONZY6149
Handicapper
Tips
Posts: 15
Joined: Mon Mar 24, 2008 12:00 am

Re: creating a btts/wbtts table in excel

Postby FONZY6149 » Mon Feb 06, 2017 11:05 am

Thanks zipster will try it out and let you know

FONZY6149
Handicapper
Tips
Posts: 15
Joined: Mon Mar 24, 2008 12:00 am

Re: creating a btts/wbtts table in excel

Postby FONZY6149 » Sat Feb 11, 2017 11:46 pm

hi zipster managed to try the countifs formula this weekend but still doesn`t give the correct results ,seems to give how many games played

Zipster
Stallion
Tips
Posts: 6224
Joined: Wed Apr 20, 2005 11:00 pm
Location: Ipswich, UK
Contact:

Re: creating a btts/wbtts table in excel

Postby Zipster » Sun Feb 12, 2017 8:32 am

hi zipster managed to try the countifs formula this weekend but still doesn`t give the correct results ,seems to give how many games played
Can you upload the file to some hosting company so I can see it, would let me sort it quicker?

Someone like http://www.filedropper.com/
OLBG Tipster Competition Assistant || A Great Man Once Said 'The first and the best victory is to conquer self' || Follow Me On Twitter

Who is online

Users browsing this forum: wonderwall