Weighted Average & Rank

Jul 25, 2007

Each individual enters 6 different qualifying numbers (columns) resulting in a "place" in each column. Placement in each of those columns is equal to the other columns or as important as the other. Is there a way to set up a formula to reflect each individuals "place" in each column and then have those columns weighted equally and averaged to determine the Winner?

ADVERTISEMENT

On Weighted Percentages - Create A Report That Rank Top Sales Agent For The Month?

Jul 29, 2014

I have been asked to create a report that rank top sales agent for the month with the following variables:

Total # of sales-35%
Total $ of sales-45%
Number of calls made-20%

Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.

View 3 Replies View Related

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?

View 9 Replies View Related

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.

View 9 Replies View Related

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.

View 9 Replies View Related

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:

View 8 Replies View Related

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.

View 3 Replies View Related

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.

View 4 Replies View Related

Weighted Average With Grades.

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?

View 9 Replies View Related

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.

View 9 Replies View Related

Criteria Weighted Average

Jan 5, 2007

I have trouble finding the right formula for a weighted average price. The formula which i used in cell B2 of the attached file doesn't exclude irrelevant numbers. In the formula "= sumproduct(a5:a21;b5:b21)/sum(b5:b21)", i like to exclude cells B8, B9, B15, B19, B20 because the list prices are missing. How can i exclude these irrelevancies in the formula above?

View 3 Replies View Related

Weighted Average Calculation

Sep 28, 2007

I just got Excel 2007 and wanted to know what formula to use to calculate the weighted averages in the attached excel file.

View 6 Replies View Related

Weighted Average Of Grades

Jan 4, 2008

I am in need of some more assistance regarding finding out a formula to find out the average grade of a pupil which ranges from letters 'A to E. This maybe be a simple task for experts, but unfortunatley spreadsheets are not my strong point. D7:D14 is the coloum that i wish to have an average of the grades acheived by each individual pupil in thier correspongin rows (on the right hand side of D7:D14) http://i41.photobucket.com/albums/e2...2/example2.jpg

View 5 Replies View Related

Reversed Weighted Average Formula?

Jun 6, 2014

What I am trying to do is rank my employees based on certain metrics. They are weighted in three different catagories. Talk time is 40%, Quality 50%, Attendance is 10%. I can weight the Talk Time and Quality, as those are basically the higher the score the better the weight. Attendance is the opposite, the lower the number, the higher the weight. Meaning, if employee has 1 absence that would better than someone who is at 4 absences. Is there a way to do this. I am somewhat educated on VBA, I understand if,then,else statements and/or Select/Case statements, so if I needed to do it in VBA I can. I just need direction to get it going.

View 3 Replies View Related

Calculating Weighted Average With Criteria

Aug 25, 2009

I have a data set that looks something like the following:

TypeAmountCost
A100$50
A200$40
A300$35
B250$40
B275$55
B500$60
A700$20
B350$25
B450$35
C400$80
C400$60

I am having trouble creating a formula that would calculate the weighted average cost depending on the type (i.e. weighted average cost of $28.84 for A, weighted avg cost of $70 for C).

View 2 Replies View Related

Weighted Average Formula Population

Jul 13, 2007

See attached picture. This shows Profit and Growth for the stores in each region.

The percentage total is weighted based on the profit of each store in the region.

What I have is an excel sheet with hundreds of regions (going down), all with a different amount of stores. I need a way to populate each of its Region % Total with the weighted average formula (in my example, cells C8, C12, and C20) while also varying the formula to include only the stores in the region.

For example, C8 calculates the weighted average for %s in rows 2 through 7 (6 stores), but C12 calculates it for rows 9 through 11 (3 stores). I need the formula to vary based on the number of stores.

View 9 Replies View Related

Weighted Average Gross Margin

Mar 1, 2012

I'm trying to calculate a weighted average gross margin but am having difficulty because I've instances of positive cost of sales and negative revenue. On a line by line basis I've had no problems calculating the margin but when it comes to calculating the weighted average margin as a whole I come unstuck. Looking at the data below (A1:D14) you can see that the margin should be somewhere between 90-99%.

Total RevenueCost of SalesGross ProfitGross Profit %4,805(449)4,35690.6%4,992(330)4,66293.4%3,467(246)3,22192.9%4,974(317)4,65893.6%3,873(130)3,74396.6
%3,756(258)3,49893.1%3,955(128)3,82796.8%2,872(59)2,81397.9%1,401(1)1,40099.9
%(10,987)21,45810,47195.3%3,042(172)2,87094.3%2,748(40)2,70998.6%2,682(172)2,51093.6%

View 4 Replies View Related

Calculate Weighted Average Sum Product

May 29, 2013

I am trying to calculate a weighted average and have used a sum product to do so. My problem is that the values decrease over time and I have done this in cells d13 and e13 and this seems to work ok. My problem is that I have many months so I don't want to use the formula this way as it will be very long. I tried to use sum and offset to try and effectively sum each row but this doesnt work.

Weighted Average

B
C
D
E
F
G

3
months

4
rate
original value
1
2
3
4

[Code] ...

View 4 Replies View Related

Linear Weighted Moving Average

Nov 25, 2006

I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.

The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.

View 9 Replies View Related

Weighted Average To Come Up With A Speed Value For Each Hour

Feb 10, 2008

I have a spreadsheet with the following data that elapses an entire year, broken down into 24 hourly sections per day:

column A: hour of the day (from 0 to 23)
column B: vehicle group (separated into "Passenger", "SingleUnit", and "TractorTrailer")
column C: average speed for that vehicle group for the corresponding hour
column D: total volume/number of vehicles in that vehicle group for the corresponding hour

I would like to apply a weighted average to come up with a speed value for each hour (i.e. combine the average speeds for each of the 3 vehicle groups based on their relative volumes across the entire dataset).

My final table should show only:
Hour (from 0-23), Avg. Speed (containing the weighted averages from the dataset)

Attached is a screenshot example.

View 9 Replies View Related

Weighted Average Excluding High/Low

Jan 6, 2009

I'm trying to calculate a weighted average excluding the top and bottom value, and am having trouble creating a formula that can be dragged across.

my values are in E2:L17, and the weights are in M2:M17. Basically I want a weighted average of each column, using M2:M17 as the weights.

I'm sure someone's done something like this before, care to assist?

Example:

Data: 1, 2, 3, 4, 5
Weights: 3, 4, 5, 6, 7

Weighted avg ex hi/lo: (2x4+3x4+4x6)/3 = 44/3

View 9 Replies View Related

Calculated Weighted & Conditional Average

Jan 22, 2009

The attachment is only a small portion of the data I need to analyse, but is representative of the full set of data. What I am trying to do is calculate a weighted average of each of the chemicals shown by location and machine usage and also still be able to use the filter or sort funtions. I have created a sample calculation in cell F9 to further explain this calculation.

I would like to be able to use the A-Z sorting icon in any of the columns, while still computing the weighted average of the respected chemicals. As it is now, the referenced cells just remain as the 6-7 cells above the calculation instead of the 6-7 cells that apply to the specific chemical at the location and machine (in this example - caustic at location1 on mch-03). For example, if I try to sort in column F, I'd still like to see my example calculation to reference the same values as it is now, calculating the same resulting 60.719.

View 3 Replies View Related

Weighted Average With Text Criteria

Sep 22, 2006

how to do a weighted average with a text criteria. I have 3 columns and many rows. The first column has either "buy" or "sell". The thing is I need to find the weighted average based on the product of the other 2 columns for buy and sell separately e.g.:...............

View 5 Replies View Related

Weighted Average After Filter Selection

Jan 11, 2007

I am looking for a formula that calculates the weighted average list price. In the attached file you'll find a formula which i've got through Ozgrid. The formula in cell I1 is: sumproduct((i4:i69)*(j4:j69))/sumproduct((j4:j69)*(i4:i69<>"")
That formula is perfect untill i filter data. For example:

The weighted value for the entire datasheet is 25.83%. After i made a filter selection on France the weighted value is still the same. How can i modify this formula so that it only calculates the weighted average for the selected records?

View 6 Replies View Related

Weighted Average Of Specific Cells In Column?

Dec 30, 2013

I would like to find the weighted average of the values in the "score" column. Additionally, I would like to only find the weighted average if the student is jack and jane. I have attached my workbook for review.

View 6 Replies View Related

Dollar Weighted Average Sumproduct Formula:

Dec 1, 2009

I've been trying to figure out a dollar weighted average formula in excel.

See attached file...

In sheet 1, I need to pull data from sheet 2 using a sumproduct formula to find the dollar weighted average maturity of a bond portfolio. Basically, I need to know how many days the portfolio has left to mature according to the portfolio's weighting by the amount in column H.

View 6 Replies View Related

Weighted Average Filter - Table Reference

Jan 28, 2013

See attach file.

I have a table named 'tbProd' and in cell D2 I have this formula, that allows to calculate the weighted average whether I filter or not by 'Dept':

=SUMPRODUCT(SUBTOTAL(9;OFFSET($D$5:$D$50;ROW($D$5:$D$50)-MIN(ROW($D$5:$D$50));0;1));$C$5:$C$50)/SUBTOTAL(9;$C$5:$C$50)

This formula works well, but I would like to change it, using table references instead of cell references.

exf_Weighted Average Filter.xlsx‎

View 1 Replies View Related

Resolving Weighted Average #Value Error In Worksheet

Jun 2, 2008

I have created a spreadsheet containing 10 different worksheets and 11th worksheet is created to show summary results of all first 10 worksheets.

Based on the situation sometimes the summary table (on each worksheet) could have "NA" Not Applicable value/text in the summary table (summary table has three distinct rows showing result of each category.

For resolving the problem please review worksheet # 10 and advise solution:

I have resolved the issue of "NA" for the first row in summary table (c25), but I cannot resolve the formula for C26 and C27. I.e., if there is "NA" in either C26 or C27, the result in C28 comes up as #Value.

Secondly, there is another worksheet named "results". This worksheet is created with an objective to show consolidated results in weighted average(summary results for each worksheet from 1,2,3.....10) but I am lost at which formula to use to get weighted average in this worksheet.

View 10 Replies View Related

Calculate Weighted Average Of Prices For 12 Months

Jul 9, 2012

I wrote code that works just fine, however, I realized there were parts of the data I was using that had 0's in it for holiday.

Bottom line, this is code that calculated the weighted average of prices for 12 months (12 rows across) and Drows across. I'm trying to use the "do while is empty" approach but can't get the syntax to work. Here's the code

Code:
For r = 1 To drows
Totwavg = 0
Tothours = 0
priceavg = 0

For n = 1 To ncontracts

[Code] .....

View 1 Replies View Related

Sort Independent Weighted Moving Average

Dec 2, 2012

The following code works fine if the data is sorted from oldest to newest. I need for it to work when the data is sorted from newest to oldest. For this to work, I would need a change that works something like this: "total1 = total1 + close_(a, 1) * a" would be "total1 = total1 + close_(a, 1) * b" with "b" incremented in the opposite direction of "a". I code very infrequently & have tried a lot of different things, searched the Internet, etc. all to no avail.

For instance, with "close" data in column "I", the data sorted as follows works with the formula
"=WMA(OFFSET(I2,0,0,9,1) )" to correctly return "791.50", but the opposite sort returns the wrong number.

The correct calculation is: (807.19*9+798.38*8+793.81*7+793.06*6+776.28*5+769.48*4+773.2*3+789.01*2+793.87*1)/(9+8+7+6+5+4+3+2+1).

DateClose
11/12/2012793.87
11/13/2012789.01
11/14/2012773.2
11/15/2012769.48
11/16/2012776.28
11/19/2012793.06
11/20/2012793.81
11/21/2012798.38
11/23/2012807.18

Code:
Public Function WMA(close_)
total1 = 0
n = WorksheetFunction.Count(close_)

divisor = (n * (n + 1)) / 2

[Code] ..........

View 1 Replies View Related

Weighted Average Inside Group Of Cells

Dec 5, 2012

I have a sheet with columns for the transport number, the invoice for the each good transported, the freight for each good and the weight.

Problem is, the weight is the total weight of the goods in each transport.

I need a macro that creates a new column that divides the weight based on the freight cost for each invoice in each transport - a weighted average.

View 1 Replies View Related

Easy Weighted Average For Thousands Of Items

May 29, 2008

I am trying to calculate weighted average cost for a list of thousands of items.

I will try to explain this as best I can.

Column A is item. Column B is Mode. Column C is Volume. Column D is Cost

There are only 2 Transmode Per Item. So each item is pretty much listed twice and I need to calculate the weighted average cost per item.

I know how to use the SUMPRODUCT function to calculate weighted average and could slowly go through the spreadsheet item by item calculating this, but is there a way to do this with some sort of formula? Similar to a Subtotal that would recognize each change item and calculate the Weighted Average at that change.

View 9 Replies View Related

Formula For Possible Weighted Average Or % Completed For A Process

May 6, 2009

I am trying to figure an easy way to create a formula or possibly even a simple macro to determine % completed of a particular activity (milestone) within a process. Within each activity there are 5-10 items that need to be accomplished. Each Activity could have a different number of items. In order to provide status at an activity level, each item within the activity will be designated with an N/A (0%), Not Started (0%), Just Begun (25%), Half Complete (50%), Wrap up (75%), or Completed (100%) from a drop down box that the user will select. Each one of these criteria can have a weight assigned to it that is in parenthesis. How do I come up with a formula based on the choices above to reach an overall percentage for the activity at any given time and then tell me status level in descriptive terms of the overall activity....

View 9 Replies View Related

Formula That Will Provide A Weighted Average Rate

May 14, 2009

I would like to a formula that will provide a weighted average rate (yellow cells) for a provided volume level, given the tiers on the left. I have already performed this the "dumb" way with lots of IF statements to figure the volume by tier and then taking a weighted average of the products with the rates by tier, but there's got to be a more efficient way. My file is huge! ....

View 9 Replies View Related

Multiple Criteria Text Weighted Average

Dec 28, 2007

It is a database in which each row represents a unique individual.

Columns A - G contains information which is irrelevant for this question
Columns H - CQ contains answers to the individual's questions (Yes/No or "-" for N/A)
Column CS contains a array formula which determines the % of "Yes" answered only in Question 1 (Columns H to X).
The formula finds all "S1*" (within H1 to CQ1) and if the corresponding "S1" has a "Yes" (within eg. row 2 - H to X) it adds it and divides by the total number of "S1" (17 in this example excluding the "-")

Eg:
In Row 2: S1 = 17, Yes = 7, "-" = 0, % = 7/17 =35.29%
In Row 3: S1 = 17, Yes = 8, "-" = 1, % = 8/16 = 50%

So I have the array formula in column "CS" which does the above.

What I want to achieve:

I want to get the average of all the individual rows in column "CS". This is shown in "CS1 (48.32%". I am looking for a way I can achieve this with just one formula - A formula which first calculates the individual %s and averages the %s within the same formula.

View 7 Replies View Related

Conditional Weighted Average Within Time Span

Feb 23, 2008

have the follwing worksheet"

Date Rate Qty
2:06:56 PM 199.5 116
2:06:49 PM 199.5 343
2:06:40 PM 199.5 226
2:06:28 PM 199.5 48
2:06:20 PM 199.4 162
2:06:11 PM 199.5 95
2:06:04 PM 199.4 449
2:05:58 PM 199.6 1488
2:05:40 PM 199.5 9
2:05:40 PM 199.5 9
2:05:37 PM 199.4 161
2:05:28 PM 199.6 101
2:05:23 PM 199.35 4605....................................

I would like to calculate the weighed average rate( rate*qty/ total qty durig a period) for different periods say 2.00 pm to 2.01 pm, 2.01pm to 2.02 pm etc. Is there a way to do this by using database(dsum,daverage) functions. What will be the content of the criterion range? Or is there any other way of solving this.

View 2 Replies View Related

Weighted Average / Dynamic Range / Conditional Dashboard

Mar 27, 2014

The drivers are simply a start date and an end date.

For example: 1/1/2014 - 3/31/2014

To keep it simple I am only looking at 4 fields in the referneced data set. They are Date, Net Calls, Average Handle Time and Agent.

So records look as follows:

DATE AGENT NET CALLS AVERAGE HANDLE TIME
1/1/2014 Agent 1 30 Net Calls 2.00 Minutes
1/1/2014 Agent 2 40 Net Calls 8.00 Minutes
1/2/2014 Agent 2 05 Net Calls 25.00 Minutes
...etc

I am stuck on the Average Handle Time problem as this needs to be weighted on net call volume contribution by agent conditional on dates being equal to or greater than the start date and less than or equal to the end date.

I have attached a workbook for reference : Dashboard Table miacg.xlsx‎

View 1 Replies View Related

Multi-Condition Volume Weighted Average Calculation

Sep 13, 2006

I am trying to figure a way -- preferably in a single- cell formula -- to calculate a conditional volume-weighted average of a series of associated prices and quantities located in two separate columns based on the date (or dates) the transactions occurred.

I can do a simple Vol. Weighted Average (VWA) of all the prices and quantities over the five-day period with the following formula: (I have created named ranges for the price column (A1:A30=price), the quantity column (B1:B30=quantity) and the date column (C1:C30=date):

= SUMPRODUCT(price, quantity)/SUM(quantity)...

View 9 Replies View Related

Calculate The Weighted Average Of The Win Rate Based On Volume Of Calls

Nov 23, 2009

I have 3 sets of data for two different groups:

Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?

View 6 Replies View Related

Rank A List Then Re-rank The List While Excluding Certain (or By Criteria) Items

Dec 30, 2013

I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items

Vendor Co
Cost Fee
Rank

Vertox
500
4

BV
1520
3

[code].....

View 4 Replies View Related

Multiple Ranking: Rank The Values In Column B And Then Rank The Values In Column C

Feb 11, 2009

What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.

ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023

View 4 Replies View Related

ADVERTISEMENT