# 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....

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....

ADVERTISEMENT

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

Consider this example: ....

We 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 RelatedIn 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.

excel macro and i would like to ask u if i want to calculate a median of column of data and then the result i need to put in a new column with row by row....i need how to write the coding?

View 3 Replies View RelatedI 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] ...

I haven't been able to find the answer to this here, nor have my own fumblings worked... I would like to have a formula to calculate the MEDIAN value of a column's visible cells - or a formula which will include the only cells meeting a certain criteria from another column.

For example...

Column O contains a list of countries: US, China, England

Column M contains the values: 1, 5, 10, etc

I would like the result of the MEDIAN formula to change based on the criteria selected in column O (so if I pick US, only the values associated with US lines are included).

I could probably put a button on the sheet and do something in vba behind the scenes, but the user is specifically asking for a formula so he can maintain it himself.

a column represents quantities and their measurement frequency in another column.

Using excel functions, how can I calculate the mean, median, and standard deviation

How can I find the median in a filtered list of numbers?

I don't see median as a function of subtotal.

On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.

How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?

=SUMPRODUCT(B2:B11,C2:C11)

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] .....

Formula for computing the weighted standard deviation? I was able to calculate the weighted average (16.4) but have been unsuccessful in computing the weighted SD. I conducted numerous searches and have not been able to find a posted formula that actually works. My data are as follows:

Year Shootings Weight*

2003 4 1

2004 17 2

2005 14 3

2006 19 4

2007 18 5

*The reason I set up the weights the way I did is because I want to give more weight to the more recent years of shootings when comparing them to other years such as 2008, 2009, 2010 and so on.

I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.

View 3 Replies View RelatedIs there a formula to calculate the median of a dropdown (Alt+DFF) selection?

E.g.:

a

1

b

2

c

3

a

4

b

5

c

6

a

7

b

8

c

9

If I select a the answer should be 4, c should be 6.

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?

It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.

So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.

I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.

Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.

The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.

I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.

I assume there is an array formula that could do this? I've tried a few solutions with no luck.

loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.

View 4 Replies View Relatedhow 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 $

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?

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.

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.

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.

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.

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.

I 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.

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

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 RelatedI'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).

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.

Copyrights 2005-15 www.BigResource.com, All rights reserved