# Weighted Scoring

Mar 15, 2008

I'm doing a GCSE project for which I need a little help with Excel.

I have a set of data about a persons monthly spending habits on

Food 300
Entertainment 200
Clothing 100
Transport 260

The pre calculated budget allowance for the month was 500

I would like to write a formula that would do the following; give the person an score out of 1.1-10 based on how well he spent on the above items which are in order of priority (i.e. it is better to spend more on food than on entertainment etc) And then an over all score (1.1-10) that will reflect how well they spent.

## Scoring Conversion

Nov 4, 2008

If I have a scoring grade for different categories (8 in total) & the grades looks something like that: 1a=500, 1b=400, 1c=300, 2a= 250, 2b=210...& so on till 4c, but with different numbers for each category.

I need to come up with a total score of points if a person is given a certain grade from the above. ex. if they scored 2a on a certain category, 1c on
another & with all the rest of 8 categories scored. The number i need to come up with is the total points.

## Scoring Programme

Apr 9, 2009

I am looking for helping with an excel scoring programme i am designing. The programme is ready to go. But i was wondering how clever excel was. Basically i need to create some results from the programme. Normally i do filter auto filter, which is great but not very fast if doing it manually. Would excel be able to do this.

The data i am using is always changing. I would need for it to filter the three teams, by a name defined in three cells one page. Once the three teams have been filtered i then need to get the top four scores and copy and past them into a new sheet.

I have tried recording a macro, but this does not seem to like me changing data. And is also very restrcitive. As the programme is used across three league.

## Formula For Scoring

Apr 16, 2006

I am not so sure on the correct formula syntax or the correct terminologies to ask this next formula question. It is visualy explained clearer in the spreadhseet provided.

## Surrounding Scoring Data

Feb 16, 2009

I have a small query about excel and whilst there is a really simple answer i'm struggling a little bit. I have create a score metric for data surrounding search data.

Basically i need a macro/formula that will take a list of values that may include duplicates and add up the corresponding score and place the results in a table.

See below - the macro will look at a list of values (column b) and add the scores up (column d) and then remove the duplicates and populate the score in (column f/g)

## Scoring To Break Tie (i.e. Golf)

Oct 7, 2006

I couldn't find a solution to where I'm at now, but Derks formula...
= SUMIF(\$J\$5:\$J\$44,J5,\$K\$5:\$K\$44)/COUNTIF(\$J\$5:\$J\$44,J5)

from...
excel formula

...Almost gets what I need, but my data will not always be in a high/low descending order. I'm braindead; I can't think of how to make this formula work.

## Society Scoring System Template

May 1, 2013

How to delete all the players names and rounds etc. so I can input all my own.

This template is perfect for what i need for my golf society.

Jan 15, 2010

I'm creating a basic Dart scoring spreadsheet, and its been going absolutely fantastic, but then I was asked to create the Sets and Legs, thats when I got stuck.

I'm looking for a formula (or anything) that will add a Set when the Legs reach the number 3, and then again but make it 2 if that player gets another 3 legs. If you know darts you will understand what I mean.

## Relative Scoring Of Text Values

May 27, 2006

I am putting together a worksheet and analysis sheet for relative scoring of text values. (i.e. High = 3, Med = 2, Low =1, Yes = 2, No = 1, etc. I have a problem in finding a formula that will work to retain these values of text and calculate a relative score from the answers or text provided.

## Scoring System. Different Points For Win, Lose Or Tie

Apr 23, 2007

Have used excel for a while but never the need to go past SUM or AVG, etc.

Im trying to set up a point scoring sytem for 3 teams in various events. the highest scoring team would get 2 points, 2nd place 1, 3rd place 0. If 2 teams tied for first they both get 2. If they tie for second they both get 1. I spent 30 minutes before I came up with

=IF(AND(B3>B4, B3>B5),2,0)
and
=IF(OR(B3=B4, B3=B5),1)

Spent another 15 trying to figure out how to put the 2 together. Finally realized a)Im missing a whole bunch of formula, b)There is a lot more to excel than I thought I knew.

B3,B4,B5 are the teams scores. Looking for any help at this point. Even a push in the right direction.

## Specify Which Criteria To Count In The Scoring As They Are Not Always Relevant Every Time

Oct 29, 2008

I have a scoring system I monitor in excel (I have attached an example speadsheet). I score against a number of criteria e.g. criteria 1 scores "2", criteria 2 scores "4". My problem is that the criteria don't always attract a score, sometimes they blank. I also need to specify which criteria I want to count in the scoring as they are not always relevant every time.

I need formulae that will take this into account and be ready to total up only when a score is there (COUNTIF?); and out of the range of scores I want to total up only the specified criteria at choice (SUMIF?).

My COUNTIF and SUMIF skills are failing with this which is very complex for my level.

What would the formula be in each instance? I have attached the file to reference the cells that I have questions about.

Question ?A: - is this a COUNTIF?
In this cell I want to calculate the number of occurrences where there is a score detailed, but only for specified criteria, for argument's sake criteria 1,2,3,4,7 & 8

The answer would be 5 in this instance, not 6, because Criteria "3" did not receive a score.

I would want to have other cells which would calculate the number of occurences where there had been a score but using different combinations of criteria

Question ?B: - is this a SUMIF?
In this cell I want to calculate the total sum of scores for those specified criteria where there has been a score
The answer would be 13 in this instance (3+2+4+1+3 from the scores that occurred for those specified criteria)
I would want to have other cells which would calculate the total sum of the scores but using different combinations of criteria

## Creating A Complex Scoring System On Excel?

Jan 8, 2014

create a unique scoring system on a set of given criteria that enables an even split in work for team members. so i.e. work comes in, and depending on the criteria of work its then passed to the relevant team member based on points. At the moment work is allocated A-Z which is working out unfair on team members as some are getting more/less work than others...a points system will enable a even split. so if a piece of work scores 30 points it goes to a junior member, if a piece of work scores 80 points it goes to a senior member and so on...my problem is how to link the points and criteria...Im guessing joint vlookups will be needed.

the only other way i can think a system is similar is like a fantasy football system...points based on criteria.

## Golf Scoring But Have Duplicate Scores - Need To Return Unique Name

Jul 28, 2014

I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.

Here is the formula I am using in Column N:
=SMALL(\$L\$2:\$L\$21,1) This runs from 1 thru 10 for the 10 lowest scores.

Here is the formula I am using in Column O:
=INDEX(\$A\$2:\$A\$21,MATCH(1,INDEX((\$L\$2:\$L\$21=\$N2)*ISNA(MATCH(\$A\$2:\$A\$21,N\$2:N2,0)),0),0))

Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX(\$A\$2:\$A\$21,MATCH(SMALL(\$L\$2:\$L\$21,ROW(N1)),\$L\$2:\$L\$21,0))

Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order

Spreadsheet Attached : Golf Excel Score.xls‎

## Prepare Automatic Scoring Sheet With Pointing Chart

Aug 17, 2014

this is a competition scoring sheet

i want to add this automatically

when i enter chest number then the name of winner and team name want to come automatically and the pointing also (see sample)

point first 5 point second 3 third 1

how to do this

## Golf Scoring But Have Duplicate Scores - Return Unique Name

Jul 28, 2014

I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.

Here is the formula I am using in Column N:
=SMALL(\$L\$2:\$L\$21,1) This runs from 1 thru 10 for the 10 lowest scores.

Here is the formula I am using in Column O:
=INDEX(\$A\$2:\$A\$21,MATCH(1,INDEX((\$L\$2:\$L\$21=\$N2)*ISNA(MATCH(\$A\$2:\$A\$21,N\$2:N2,0)),0),0))

Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX(\$A\$2:\$A\$21,MATCH(SMALL(\$L\$2:\$L\$21,ROW(N1)),\$L\$2:\$L\$21,0))

Column A = golfers names

Column L = list of all golfers scores

Column N = lowest ten scores in order

## How To Do A Weighted Median?

Nov 2, 2006

Is there any way to do a weighted median in Excel?

Consider this example: ....

## Weighted Average

Jul 25, 2007

I have a large dataset that includes information on the investment payout ratios of US companies over the period 2004-2006. I need to calculate the weighted average investment ratio for each company---see sample below

Microsoft Excel - example.xls ....

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The corresponding weight for each year must be as follows: 0.5 for 2006, 0.3 for 2005 and 0.2 for 2004. Then, for company A, the weighted average will be: (0.5*B2)+(0.3*C2)+(0.1*D2) = 7.8 (see cell E2)

The problem is that for some companies there are missing data for some years. For example, for company B, there are no data available for year 2004. In this case , the weights have to change to 0.6 for 2006 and 0.4 for 2005. Then, the weighted average investment for company B will be (0.6*B3)+(0.4*C3) = 16 (see cell E3). Similarly, in cases when there are data for only 1 year , the weight has to change to 1. Then, for company C the weighted average is 35 (see cell E4)

Is there any formula to give me the weighted average as it appears in column E without having to adjust it for each single case?

## Is This A Weighted Avg Formula

May 1, 2009

In Col DL are digits ranging from 1-36, in Col Dm are the number of times they were drawn in 36 draws. I have a weighted avg formula in Col DN, that I found thru search, but it doesn't appear to be working right or maybe I don't understand the formula. I have no idea if this can be done.

************************************************************************>Microsoft Excel - F5 HOT-COLD.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutDN3DN4DN5DN6DN7=DLDMDNDO2DIGITSHITS 34125 47105 53395 62185 72885 DIGITS [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

## Weighted Average In VBA

Nov 10, 2009

I have a spreadsheet which basically looks like this

2, 5, 15, 20
2, 8, 12, 13
2, 7, 19, 46
F, 1, 10, 26
P, 1, 10, 39
P, 6, 22, 89
P, 7, 14, 46
P, 2, 11, 14
...

Column A is the key. If multiple rows have the same key, they're related.

The last column (DX on my spreadsheet) represents minutes participated, and is the weight variable. The middle columns are data that I'd like a weighted average for.

Here is the output I'm looking for given the above example:

2, 6.66, 16.83, 79
F, 1, 10, 26
P, 4.91, 16.73, 188

Column A remains the same, with one row for each key. The middle columns have their weighted average (where 6.66 = (5*20+8*13+7*46)/(20+13+46)), and the final column (DX) is the sum of minutes for all like keys.

## Weighted Average ...

Nov 16, 2009

I have long lists of data which has name of manufacturer, number of items and fault rates of items.

E.G

Manufacturer.......Number of Items......Fault Rate
Dell....................40000....................0.2%
Dell....................42200....................0.4%
Sharp.................303003..................0.1%
Microsoft.............312340..................0.04%
Dell....................3249321..................1%
Panasonic............300020...................2%

I need to find the weighted average of each manufacturers fault rate for just themselves, and also how the average of all the manufactueres would be if they weren't in it.

So for example for Dell I would need their weighted average of their three data entries and also then the weighted average of Sharp, Microsoft, Panasonic together.

I have been doing this by splitting the data into sections so for the example above I would do:

Manufacturer.......Number of Items......Fault Rate
Dell....................40000....................0.2%
Dell....................42200....................0.4%
Dell....................3249321..................1%
Panasonic............300020...................2%
Sharp.................303003..................0.1%
Microsoft.............312340..................0.04%

I would sum the Dell items, then sum the number of items excluding Dell then do two weighted average calculations giving me the ones that include then the ones that exclude Dell.

Then I would do...

Manufacturer.......Number of Items......Fault Rate
Sharp.................303003..................0.1%
Microsoft.............312340..................0.04%
Dell....................3249321..................1%
Dell....................40000....................0.2%
Dell....................42200....................0.4%
Panasonic............300020...................2%

And do the same process but with Sharp totals etc then move onto the next one.....

This example is with a very small data set so I'm sure you can see that this process is very time consuming, but I don't know any other way of doing it without splitting out the data first.

## Weighted SUM By Criteria

Jan 22, 2008

I am trying to find a formula that will calculate a running total of employee time off. Time off is recorded in Excel spreadsheets that I created. V = vacation, S = sick, PD = personal day, and so on.

For example: If a V is entered in any cell in their attendance worksheet, I would like for a 1 to display under the vacation total column. The next time a V is entered in, I would like it to update the cell containing the vacation days used for the year to 2. If a 1/2 V is entered, a .5 would be added to the total used.

## Weighted Numbers Greater Than Whole

Apr 15, 2014

I have a set number that I need to allocate over a flexible period of say 5 -11 days.

I have applied the traditional split per day to each number i.e. a monday normally results in 20% of transactions, Tuesday 25% etc.

The issue comes in when I try to allocate the budget over a period longer or shorter than a week.

Therefore the budget for the 9 days is being allocated over the period of 9 days which results in a overall allocation of 150%. (duplicate mondays etc).

Is there a way to limit the entire period to a set number and have the numbers adjust themselves accordingly up or down to match it other than multiplying it by a factor?

Example attached: Excel Problem.xlsx

## Conditional Weighted Average

Apr 20, 2009

I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost \$100 and the profit was \$20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:

## Histogram From Weighted Data

Jan 12, 2010

I'm trying to use the 2007 histogram tool (or frequency) to show me the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"??

My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total).

## Weighted Average With Criteria?

Dec 5, 2012

Ceate a formula for weighted averages according to criteria?

I want to know what was the average price I bought a product at on today's date. The average price needs to be weighted against the quantity of product sold.

## Optimizing Weighted Averages?

May 21, 2014

I created an Excel tool into which really thousands of records are based on weighted averages calculations. And discovered Excel struggling in term of permormance.

The issue is: the user needs to uploads a couple of times in a day new data for further analysis, and the only way to make the Excel spreadsheat humanely usable (performance) was to calculate everything upfront.

To optimize the tool performance, I had to:

- First I made all weighted average calculations upfront when data is uploaded the first time. Leaving formulas in the tool was not viable, since this tool run live analysis, every click would have been a pain of 30 sec at best to re-calculate new conditions,

- Second optimization was to not leave formulas into columns > as soon as a calculation is done, the macro copy/paste values

The formula I chose is sumproduct based weighted averages..

Here is my question:

1. Does it exits specific formulas in Excel to handle nicely large amount of weighted averages to calculate ?

2. Should I consider to use an external application (an Access table, SQL or whatever) to perform those weighted average calculations quickly/quicker.

## Weighted Average By Range

Sep 9, 2012

Price
units
percent discount
weighted percent discount (1.0-2.0 price range)
1.32
100

[Code]...

So from the above example I would like to determine the weighted average for percent discount (weighted by units) by price range.

Dec 7, 2008

I am having trouble coming up with a formula to calculate the weighted average of students Finals grades. This is the format my sheet is in to give u an idea.

-----------------------------------------------------------------------
Test # 1 Test # 2Test #3Assignment #1Assignment #2Final Test Final MarkPossible Points:1001001001010100Percent Worth:20%20%20%5%5%30%Students:Anderson, Ken6473788768 ?Carter, Bill72768473.587 ?Dubeers, Ashley84887610590Jackson, Kevin7666549873Parker, Jerome82657679.560Remini, Leah969290101089Thompson, Chuck5042806.5760Vaughn, Sarah7265658970
----------------------------------------------------------------------- -
I have to come up with students averages based on the mark, the percent each grade is worth and the maximum amount of points its worth. Anyone know the formula or how to calculate the final grade using all of these factors?

## Using Weighted Average In A What If Analysis

Sep 4, 2009

If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.

## Calculate Weighted Median

Aug 22, 2008

I have a task wherein I have to separate data in the Excel sheet and then calculate its median.

For example the data is recorded as follows....