Finding the weighted average of text that have been given values


N

newbroug

Ok, so I used the "COUNTIF" formula to give texts within a chart values such as 1,2,3, etc depending on where they are in the chart. For example:


QB RB RB2 WR TE FLEX D/ST K
1 X Y W A B A C X
2
3
4
5
6
7
8
9
10
11
12

This is for a fantasy football league. The teams would fill up the chart. There are 12 different "TEAMS" within the chart, and each column has one of each "TEAM". This is simply a ranking chart from best (1) to worst (12). Sousing my "COUNTIF" formula, I have created a way to add up all the values to give me a whole number. I then use this whole number and divide it by 9 (the amount of positions along the x-axis) to give me the average ranking for each "TEAM". An example of my "COUNTIF" formula:

=(COUNTIF($B18:$J18,"LEVS")+COUNTIF($B19:$J19,"LEVS")*2+COUNTIF($B20:$J20,"LEVS")*3+COUNTIF($B21:$J21,"LEVS")*4+COUNTIF($B22:$J22,"LEVS")*5+COUNTIF($B23:$J23,"LEVS")*6+COUNTIF($B24:$J24,"LEVS")*7+COUNTIF($B25:$J25,"LEVS")*8+COUNTIF($B26:$J26,"LEVS")*9+COUNTIF($B27:$J27,"LEVS")*10+COUNTIF($B28:$J28,"LEVS")*11+COUNTIF($B29:$J29,"LEVS")*12)/9

(if there was a simpler way of doing this, I would like to know that too, but as of now I am set with this formula)

The thing is, each position along the x-axis has a certain weighted value that I would like to incorporate into the formula. For example, when your QBoutscores your opponents QB in a given week, you have a 63.89% chance of winning the game. So how do I correctly insert this percentage? Or, should Igive a whole number value to each position (i.e. RB1=1, FLEX=2, etc.)?What would be more accurate?

This my first post, so I'm not sure if I can actually upload the spreadsheet. I will if I can, but I do not see an option to do so. In the "COUNTIF" example I posted above, the number that comes out is "3.33". The weighted average that I am looking for is "2.02" when using winning percentage as the weight.

I'm not entirely sure if I explained this correctly, so if there is any confusion I would be more than happy to clarify. Thanks for your help!


--Cory
 
Ad

Advertisements

N

newbroug

I'm confused by your use of the word "chart". This normally refers to a graph or pie chart or column chart or some graphical representation of data,but it looks as if you mean data set out in a table or on a worksheet. Is that what you mean?



Howard

Thanks for the reply, Howard. Yes, I meant table instead of chart. It is simply a table that I am pulling this data from.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top