How To Do A Weighted Median?
Nov 2, 2006Is there any way to do a weighted median in Excel?
Consider this example: ....
ADVERTISEMENT
Calculate Weighted Median
Aug 22, 2008I 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....
Find Median Of Large Set Of Numbers To Calculate Median Income?
Mar 6, 2014We are trying to find the median of a large set of numbers to calculate the median income in 2010. For an example we have 8,379k people with $2500 average income, 9,783k with $7500 average income and so on. How can I calculate the median average income of such a large amount of entries?
View 8 Replies View RelatedFind The Median For A List Of Numbers Using =median()
Nov 11, 2009how to find the median for a list of numbers using =median(). But what if I have a list of prices for different types of say cars.
Car Type Price
A $10000
B $15000
A $20000
C $30000
Is there a macro that will return
Car Type Median
A $
B $
C $
Weighted Average
Jul 25, 2007I 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?
Weighted Scoring
Mar 15, 2008I'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.
Is This A Weighted Avg Formula
May 1, 2009In 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, 2009I 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, 2009I 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, 2008I 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.
Median IFS Possible?
Jul 7, 2008I have a database of employees with 7 different variables [Year, JobFunction, PreviousIndustry, PreviousRegion, Degree, Experience, CollegeMajor] of which I would like to be able to select any number of and find their median/max/min/etc salary.
For example, I may need to know the median salary of all employees who started in Year 2006, who work in accounting, and have an MBA.
------------------------------------------------------------------
Rather than copy and paste my 7 variable AVERAGEIFS function, I'm just going to use a two variable example here to keep things simple.
I had no trouble doing what I need with this 2 variable AVERAGEIFS function like so;
{=AVERAGEIFS(Salary,File,$A$3,JobFunction,$A$6)}
...where Salary, File and JobFunction are the names of a cells in another worksheet and $A$3 is a drop down menu that I can select the File Year and $A$6 is a drop down menu where I can select the JobFuction.
Now I would also like to have the same kind of conditional "IFS" filtering with the MEDIAN function, but I have been unable to figure it out since Excel doesn't have a MEDIANIFS function. This is what I've tried so far;
{=MEDIAN(IF(AND(File=$A$3,JobFunction=$A$6),Salary))}
but this just returns "0" which obviously means I'm doing something wrong, can anyone help me out here?
Note: I was able to get it to work with just a single variable just fine like so;
{=MEDIAN(IF(File=$A$3,Salary))}
but trying to add the second [and eventually 3rd, 4th, 5th, 6th, and 7th] conditions is the problem.
Weighted Numbers Greater Than Whole
Apr 15, 2014I 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, 2009I 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 RelatedHistogram From Weighted Data
Jan 12, 2010I'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, 2012Ceate 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, 2014I 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, 2012Price
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.
Weighted Average With Grades.
Dec 7, 2008I 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, 2009If 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 RelatedCriteria Weighted Average
Jan 5, 2007I 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 RelatedWeighted Average & Rank
Jul 25, 2007Each 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?
View 9 Replies View RelatedWeighted Average Calculation
Sep 28, 2007I 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 RelatedCalculate Weighted Percentage
Oct 18, 2007In my spreadsheet (testing.xls) I have a chart to mark the quality of a phone call based on a list of 8elements being judged. I have a likert scale for the quality result for each element, say for example 5 for highest, 3 for average, 1 for the poor, 0 for very poor and NA for an element which is not counted.
At the bottom of my table I have a cell where it would work out the % based on the outcome of the result from the different cells being ticked, what would i need to do to have excel work this out for me? Please bare in mind that if there is for example 1 element out of the 8 elements marked as NA the result would be divided 7 instead of 8.
Weighted Average Of Grades
Jan 4, 2008I 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 RelatedMean, Median And Mode
Apr 29, 2008Is there a good/easy formula to use when trying to find the mean, median and mode for a column? If so, can someone post it? I'd like to make it as easy as possible (obviously!).
View 6 Replies View RelatedMedian Of All Combinations
Jul 25, 2008I have 5 numbers (can be with decimals) in column A (A1:A5). For these
numbers, I want all combinations as well as the median of these
combinations.
I'm pretty far already. However, the median seems to be calculated
wrong.
Calculating Median
Jul 27, 2009I have a list of hotel IDs in one column of my spreadsheet. In the next few columns, I have ratings that customers gave the hotel for cleanliness, location, room, etc.
What I need to do is calculate the median of all the ratings for each specific hotel, in a separate column.
An example of what I have: ...
Find The Median
Nov 18, 2008I have two columns of data, one is the price per unit, the other is the quantity sold at that price. I'm trying to find the median selling price, but MEDIAN doesn't work correctly and I'm not sure how to tell the function to count each price the actual number of times it was sold.
I actually believe I've found a work-around for this problem - I sort by price and use a SUM function to figure out where the middle is...I just feel like there should be an easier way (for the future).
How To Get Median For Subtotal
Aug 3, 2012Is there a way to get median for a subtotal?
median(SUBTOTAL(1,G7:G1747)) is not working.
Calculating A Median Age
Apr 18, 2008I have a list of ages 10-65 and then different number of participants associated with each age, ie: 10 - 5,071, 11 - 6,069, 12 - 8,465, etc. to age 65. I am try to calculate the median age of all participants.
View 9 Replies View Related