Excel Newbie, Setting up a Boxing Prediction League

[LuisX]

Hello all, I'm pretty new to excel and i'm trying to setup a Boxing Prediction league. I did this last year but I had to quit early because it took to much time and I had to put the points one by one since I don't know much. Here's the situation... I'm setting up a Boxing prediction league. The league consist on predicting the winner and outcome of a fight. It got almost 100 participants and I am trying to find a way to do the calculation more quickly last year I had to put the points 1 by 1 and it took me forever, Im looking for a way that with putting the fight outcome the table can put the points without me having to calculate each one. I do this league on facebook and they put their predictions on the comments sometimes it could 5 fights in a week. An example of how they put the predictions could be: Boxing A KO 6 or Boxing B UD and so on. Any help will be well received. Here is the points criteria:


Result (e.g Boxer A, Boxer B or Draw)
The Type of win
Points - When a match goes to the cards
Stoppage - When a match stops early (e.g. TKO, DQ etc)
Method of win
If Points
Unanimous Decision
Spilit Decision
MarjorityDecision
If by Stoppage then in which Round was it stopped.


Scoring
Points for picking a Result
Prediction Points
Correctly picking the winning boxer 10 pts multiplied by the odds (ex. favorite always is gonna be 10 x 1, and not favorite 10 x 2, 10 x 3, etc. this depends on the fighters and odds)
Correctly picking a draw depends the fight (minimun 20 pts)
Not picking the correct boxer (Boxer A wins and you have picked Boxer B or a Draw) -10 pts
Picking a Boxer but the result is a draw -10 pts
Not predicting a result but has predicted other matches this week I -10 pts


Picking the correct type of win - Only counts if you had the result correct
Prediction Points
If you correctly guessed a stoppage 3 extra pts
If you correctly guessed a points result 3 extra pts


In a match going to points - only counts if you had result and type correct
Prediction Points
Correctly guessing a Unanimous Decision 3 extra pts
Correctly guessing a Split Decision 3 extra pts
Correctly guessing a Majority Decision 3 extra pts


In a stoppage Result - only counts if you had result and type correct
Prediction Points
Correctly guessing the round (1-12) 5 extra pts




The only points that changes from fight to fight are the Draw and the odds of every fight everything else stays the same. Remember Im a newbie)

 

[exceljennifer]

I saw your post somewhere else and thought you're idea could give me something to do and keep my nerd brain happy . I have a few questions about it.

 

[LuisX]

I saw your post somewhere else and thought you're idea could give me something to do and keep my nerd brain happy . I have a few questions about it.

Go ahead you can ask anything. I'm Kinda new in these forums , Maybe if you want you can email me your questions.

 

[Michael M]

Luis....you probably need to read post #4 here.....

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[LuisX]

Luis....you probably need to read post #4 here.....

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Sorry about that. I should've read the rules before. Not happening again. I'm really sorry about that.

 

[BrianJN1]

LuisX, you said that you did this last year. Therefore you have some kind of a foundation/format around which you can build. At the moment I'm struggling to understand what that might be. Maybe the row of headers and an example of data for each week. What I'm really asking is, let us see the framework in which you are trying to work.

 

[LuisX]

LuisX, you said that you did this last year. Therefore you have some kind of a foundation/format around which you can build. At the moment I'm struggling to understand what that might be. Maybe the row of headers and an example of data for each week. What I'm really asking is, let us see the framework in which you are trying to work.

Mostly what I did was data entry. I just put the numbers. something like this:

[TABLE="width: 514"]
<colgroup><col width="43" style="width:32pt"> <col width="247" style="width:185pt"> <col width="38" style="width:29pt" span="2"> <col width="40" style="width:30pt"> <col width="41" style="width:31pt"> <col width="67" style="width:50pt"> </colgroup><tbody>[TR]
[TD="class: xl37, width: 43"]Position[/TD]
[TD="class: xl38, width: 247"]Participants[/TD]
[TD="class: xl37, width: 38"]Nationality[/TD]
[TD="class: xl39, width: 38"][/TD]
[TD="class: xl37, width: 40"]Santa Cruz vs Ruiz[/TD]
[TD="class: xl37, width: 41"]Wilder vs Stiverne[/TD]
[TD="class: xl45, width: 67"] Total[/TD]
[/TR]
[TR]
[TD="class: xl40"]1[/TD]
[TD="class: xl41"]Jonex Kaztro[/TD]
[TD="class: xl42"]PR[/TD]
[TD="class: xl43"]100[/TD]
[TD="class: xl40"]18[/TD]
[TD="class: xl40"]16[/TD]
[TD="class: xl44"]134[/TD]
[/TR]
[TR]
[TD="class: xl36"]2[/TD]
[TD="class: xl20"]Jaime Soberon[/TD]
[TD="class: xl25"]MEX[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]13[/TD]
[TD="class: xl36"]16[/TD]
[TD="class: xl24"]129[/TD]
[/TR]
[TR]
[TD="class: xl36"]3[/TD]
[TD="class: xl20"]Isai Guzman[/TD]
[TD="class: xl25"]MEX[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]13[/TD]
[TD="class: xl36"]16[/TD]
[TD="class: xl24"]129[/TD]
[/TR]
[TR]
[TD="class: xl36"]4[/TD]
[TD="class: xl20"]Jose Peralta [/TD]
[TD="class: xl25"]MEX[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]13[/TD]
[TD="class: xl36"]16[/TD]
[TD="class: xl24"]129[/TD]
[/TR]
[TR]
[TD="class: xl36"]5[/TD]
[TD="class: xl20"]Franco Ezequiel Perez[/TD]
[TD="class: xl26"]ARG[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]13[/TD]
[TD="class: xl36"]16[/TD]
[TD="class: xl24"]129[/TD]
[/TR]
[TR]
[TD="class: xl36"]6[/TD]
[TD="class: xl20"]Antonio Ortiz[/TD]
[TD="class: xl21"]PR[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]13[/TD]
[TD="class: xl36"]16[/TD]
[TD="class: xl24"]129[/TD]
[/TR]
[TR]
[TD="class: xl36"]7[/TD]
[TD="class: xl20"]Gallos Ivan Porrata[/TD]
[TD="class: xl21"]PR[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]18[/TD]
[TD="class: xl36"]10[/TD]
[TD="class: xl24"]128[/TD]
[/TR]
[TR]
[TD="class: xl40"]8[/TD]
[TD="class: xl20"]Nicolas Kovacic[/TD]
[TD="class: xl26"]ARG[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]18[/TD]
[TD="class: xl36"]10[/TD]
[TD="class: xl24"]128[/TD]
[/TR]
[TR]
[TD="class: xl36"]9[/TD]
[TD="class: xl20"]Richard Peña[/TD]
[TD="class: xl27"]VEN[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]18[/TD]
[TD="class: xl36"]10[/TD]
[TD="class: xl24"]128[/TD]
[/TR]
[TR]
[TD="class: xl36"]10[/TD]
[TD="class: xl20"]Luchy Blindage[/TD]
[TD="class: xl26"]ARG[/TD]
[TD="class: xl22"]100[/TD]
[TD="class: xl36"]18[/TD]
[TD="class: xl36"]10[/TD]
[TD="class: xl24"]128[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

Ok, that's a start, but I still need more information about the inputs and why you have built this table in the way it is.

 

[BrianJN1]

Let's see. This is a table of the ten most successful tipsters/predictors. Each started out with 100 points. They have earned points for two contests and their points are added to (or subtracted from) their original 100. On Facebook what might a typical prediction be: Red Cnr TKO in Rnd4?

Looking at your opening post I see a complexity of conditions to be met so as to arrive at your points award.

The first thing it seems is that you need to build a table which describes the result of the specific encounter which you wish to address.
This is a start to what I envisage you could develop.

This would then become a reference against which you would compare what your predictors may have chosen.
As I see it you need a Members' List against which you record their scores bout by bout.
To fill the table, of which the Members List would be part, would then require building an input form that uses the VLOOKUP function to record your awarded score there.

How might that input form look?
I'd place it on the same sheet as your "Outcome" so that later reference is easy.
That could vary. Essentially you would be placing appropriate content into the form that corresponds with the fields of the "Outcome". After that you need to apply your calculations.

Apologies, but you said that you were a "newbie" and I feel that much of what I have already said may have left you behind.
Some of your calculation data (points for different occasions) could be listed on the Input Form sheet.
That said, the next step that is required almost certainly will leave you behind.
You will need to write a macro, a program, which will transfer your awarded score to the right place in the separate sheet which holds your Members' List and attached table.
At the far right of that table you create a sum of the pluses and minuses that have been awarded.

To build your League table would require you copying the Predictor ID columns further to the right of the last column. Directly beside that list you would copy and paste as "VALUES" the contents of the sum column. From there you can do a Data Sort to get your top down hierarchy.

Complicated? I don't think that I can make this any easier. At least, unless there is someone else here with better insight, may I suggest that you find a compliant grandchild, or some neighbour's child who is conversant with the rudiments of Excel to get you started.

Once you have more solid thoughts please come back. That point just might be when you've built your "Outcome" table.

Cheers.

 

[LuisX]

Hello,

Thank you for your response. Base on that the following is what I have so far. I use vlookups to feed every "score" column.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Participant	Boxer(A) always the favorite in odds (*1) Boxer(B) variance odds Draw (D)	Score	Type of Win Score Cards (C) +3 KO (KO) +3	Score	Score Cards U (unanimous dec) +3 S (split decision) +3 M(majority decision) +3	Score	TKO What Round? 1-12 (+7)	Score	Total
Juan	A	BC	AU
Jose	B	BC	BS
Jacobo	D
Julian	A	AKO		A9
Jesus	B	BKO		B8
Javier	B	BC	BM

<tbody>
[TD="align: right"]=VLOOKUP($B$2:$B$7,$B$12:$C$14,2,FALSE)[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]-10[/TD]

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

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

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]-10[/TD]

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]-10[/TD]

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

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

[TD="align: right"]0[/TD]

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

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

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

</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Result
B,KO,8	A	AC	AU	A1
	B	AKO	AS	A2
	D	BC	AM	A3
			BKO	BU	A4
ODDS			BS	A5
A	*1				BM	A6
B	*3				A7
							A8
							A9
							A10
							A11
							A12
							B1
							B2
							B3
							B4
							B5
							B6
							B7
							B8
							B9
							B10
							B11
							B12

<tbody>
[TD="colspan: 2"]Winner*ODDS[/TD]
[TD="colspan: 2"]Type of Win[/TD]
[TD="colspan: 2"]Score Cards[/TD]
[TD="colspan: 2"]What Round[/TD]

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

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

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

All these feed the following spread sheet that is the final one:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Position	Participants	Nationality	Boxer A vs. Boxer B	Total
	Julian	PR
	Javier	PR
	Juan	PR
	Jesus	PR
	Jose	PR
	Jacobo	PR

<tbody>
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]

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

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

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

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

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

</tbody>

So, my question is:How can't I make this spread sheet to sort by itself by total every time that I enter final numbers to each fight? Do you think that there is a simpler way to make the spread sheet?

Best Regards,

Luis X.