Calculate Weighted Averages Using High, Medium, Low
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?
View Complete Thread with Replies
Related Forum Messages:
Weighted Average Excluding High/Low
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?
Data: 1, 2, 3, 4, 5
Weights: 3, 4, 5, 6, 7
Weighted avg ex hi/lo: (2x4+3x4+4x6)/3 = 44/3
Weighted Averages Using SUMIF And COUNTIF
DATA: D1:D11 contains either blank cells or a dollar figure from a payroll check.
E1:E11 contains either blank cells or the date of the check.
Current Situation: Using the following formula I obtain a weighted average of the pay checks, not including the largest. =(SUM(D1:D11)-MAX(D1:D11))/(COUNT(D1:D11)-1)
I desire to calculate the weighted average based on the checks issued only prior to a certain date. I have used SUMIF() in other parts of the workbook successfully but needing to withold the largest value has presented me with a problem I can not find a solution to. =SUMIF((E1:E11,">="&TargetDate,D1:D11)-MAXIF(E1:E11,">="&TargetDate,D1:D11))/COUNTIF(E1:E11,">="&TargetDate,D1:D11)
Find The High Price Day Of Each Week. Then Report The Median High Day
I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....
Calculate Weighted Percentage
In 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.
Function To Calculate Averages Of Percentages
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
Calculate A Lot Of Averages Of Non-connected Values
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
Calculate Averages Based On Where Names Are Positioned
The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].
My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.
How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
Get The Last, Say 4 Values For The Min, Max And Medium Value
My problems is, and this may be sloved in excel or with VBA. What i have is a number of results, Load and displacement takes at intervals in time. An example of data is below. I will know the min, medium and max numbers for load. What i would like to do is get the last, say 4 values for the min, max and medium value
for example, with the below data,
1 (last value) 1
1 (last but one) 1.21
1 (2nd last) 1.2
210 (last value) 69.1
210 (last but one) 69
210 (2nd last) 68.9
how can i do this?
Date and time of resultLoadDisplacement
10/06/2006 12:05 10.2
10/06/2006 12:10 9010
10/06/2006 12:15 21050.........
IF Function: Range That Should Display 'Heavy', Medium Is Displayed Instead
=IF(('MAIN 3'!N54<=H52),"Light",IF(('MAIN 3'!N54>H52,N54<H54),"Medium",IF(('MAIN 3'!N54>=H54),"Heavy",""))). The first two parts work fine: =IF(('MAIN 3'!N54<=H52),"Light", IF(('MAIN 3'!N54>H52,N54<H54),"Medium", Light and Medium is displayed when I enter the appropriate range but when I enter a range that should display 'Heavy', Medium is displayed instead.
Sort High To Low
I have the following table...
NameQuestions correctQuestions WrongENTERPositionNameQuestions correctQuestions WrongPoints1dave6216203ben531240506jan712070809dan802410011fred44812tom354
When the data is typed into the top box, I have a macro for the "enter" button that places the data into row 12.
What I really need is after the data is placed into row 12, is for the table to be sorted highest points to lowest points, and if scores are the same then in alphabetical order.
This is my macro......
Application.CutCopyMode = False
Find Intermittent High Value
I am analysing market data to determine when trades should take place and when they should be closed. This works well. But I need to find the intermittent high value between the dates for purchase and the closing trades, and then insert these values in column S in the attached worksheet.
I don't know if a function can find these values or if a macro is needed (or if I should amend the existing macro that currently finds the
This macro is included in the attached spreadsheet. It Is:
Conditional Format For Low, Mid & High
Sorry I don't mean to bring up old post of mine but my boss wanted me to change how this works. I have placed a link to the old post of mine in hopes that it may help. Instead of having two conditions that turn green, he wanted to combine those two, have the lower turn red and the higher turn yellow. Still only three colors but I am unfamilar with how these conditional formats work.
Sort The Addresses From Low To High
I have voter registration pdf where I can open the table of info into excel when I can edit it. When I try to sort the addresses from low to high. example I have 3 main st, 20 main st, 78 main st, 300 main st. When I try to use sort to arrange them from low to high it does not work correctly do you think I am having a problem when adobe opens it to excel or am I doing something wrong?
High Scores And Tiebreaker
This is for a forestry contest. Students compete in 6 different categories (columns B through G). Teams can have up to four members and the top three member scores are taken to determine a team score. Teams are then ranked. Here is where I need help. I would like to be able to find the high individual score for each category in the contest. (sawlog, pulpwood, tree ID, etc) If there is a tie in the category it is broken using the category in the column to the right. Example – sawlog 1st tie breaker is pulpwood, the 2nd tiebreaker is thinning. The last column, Compass, uses sawlog as 1st tie breaker and pulpwood as 2nd tiebreaker.
Will on the RHS team had a score of 112 in Tree ID and a score of 48 in thinning.
Michael on the WHS team also had a 112 score in Tree ID and a score of 44 in Thinning.
So Will is the high individual in that event –
How can I create a formula that will bring the winners name down to row 66-71 and also bring the score down to row 66-71 to show who won each event? example spreadsheet attached.
Low And High Numbers With Stats
I seem to find myself stuff on this project yet again, JBeaucaire was able to help me to get this far im hoping you will be able to help me with the next step of this project
The doc iv attached is able to give me the differnece between low and high numbers with this example low numbers being between 1-18 and 19 – 36 high numbers and 0 being a break if u test the project u will understand im now trying to create a pattern of 3s were im abe to get stat hits on for example the patterns of 3s are the following(numbers) low,low,low-low,low,high-low,high,high-low,high,low every time I type in the numbers 3 times in a row on the 3rd one I want it to show as a hit(success) if the 3 numbers I type in are not a hit like high,high,high I want it to show me a miss if it is a pattern I want it to show a if theres a 0 it must rest like from 0miss,4hits,o breaks to 0miss,0hits,1break and so forth im also wondering if at another part of the form it can keep the total stats like iv added In the doc
High Frequency Data Calculation
Suppose you have a large database, which consists of high frequency minutes data, you need to calculate differences in values between each different time intervals, e.g. from 1 min to 10000 mins.
the format is looked like below
1 min aaa
2 min bbb
3 min ccc
4 min ddd
More specifically, what I am supposed to do is to calculate aaa-bbb, bbb-ccc, ccc-ddd, and then aaa-ccc, bbb-ddd, and then aaa-ddd and so on.
I know the VBA has a function to do repeated executions but am not sure how to adjust the time intervals.
Find & Return Weekly High Value
I want to find the high value from daily data for each week and copy that value.
I have a list of daily price data, being the date, opening price, high price, low price and closing price. For each week I am finding the data necessary to create a list of the weekly opening price, high price, low price and closing price ending on each Friday.
To find the opening price I am using this formulae -
To find the closing price the formulae is -
I need to find the high value and the low value within the five days data, from two separate columns (C and D) of daily data.
Can I use a formula similar to those above, but instead of copying a relevant cell, for example, finding the high from cells B3 to B7. Then I need to do this for each subsequent week.
An example spreadsheet is attached. You will see that currently I am currently copying the Friday data in columns L and M as I don't know how to adapt the formula to do the job.
Picks Up The Drifferences Between Low And High Numbers
picks up the drifferences between low and high numbers for example 1-10 is low 11-29 high and 0 is a alternate so say i start typing in 1,2,3,11,15 in the top cell they start decending down the rows automtically and the program has 2 boxs indicating 3 lows and 2 highs and wen i type in a 0 it resets the two boxs to 0
Lookup Number Between High/Low Range
I have two columns of numbers (I'm going to call them Column A & Column B). There are 23 rows with these numbers. There is another column (Column C) which is also 23 rows of numbers. I am trying to find a way for a designated cell that falls between the two numbers in Column A and Column B to display the number in Column C. The numbers would work their way across the row. For example, if cell A1 had a value of 1 and B2 had a value of 10 and cell c3 had a value of .034, if the designated cell has a value of 7, I would want the cell below the designated cell to show the value of .034. I can't use the if/then function because of the 7 limit. I also can't use the vlookup because there is a range of numbers instead of an equal to.
Averages From 2 Columns
We are trying to get an Average between 2 columns that have the same conditions. We have been using an array formula succesfully on data that is on 1 column and I have trying to incorporate an Offset into the formula with no luck.
Here is our latetest attempt, WITHOUT THE OFFSET.
The data is in Column P and Column N
=AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650>.01)*('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650<.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650)))
Averages For Last 12 Entries
I need to calculate average for last 12 valid entries in a column. I say valid because if there are blanks or zeros in the cell they need to be discounted.
The solution needs to work on percentages as well.
To explain further
Col A Col B
Jan 2007 1
Feb 2007 5
Mar 2007 5
Apr 2007 0
Jun 2007 20
Jul 2007 0
Sep 2007 10
Oct 2007 0
Nov 2007 10
Dec 2007 18
Jan 2008 8
Feb 2008 6
Mar 2008 0
May 2008 10
Jun 2008 1
The requirement would be to calculate average from Jun 2008 backwards for 12 months where there aren't zero or blank entries.
I am inputing each day the total amount of sales. I am manually doing the averages for past 12 weeks on Monday sheet. I would like when I enter the amount for that week that the cells B84, C84, E84, F84 are automatically updated for the past 12 weeks. I have tried a few different approaches but I keep getting #ref or circular reference
I am working on a spread sheet and am trying to use rolling averages of the last 3 months. I am using worksheets for each month with a final worksheet containing the 3 mo. rolling average. I tried using a formula with OFFSET and it worked fine if all the data was in 1 worksheet, but did not work for multiple worksheets averaging to another worksheet in the same workbook.
In the following cells I have an average:
B6,J6,R6,Z6, and AH6 and a total average on cell AP6
How would I go about making a formula for that. The reason why I ask is because all the info I have seen refers to the cells with the averages need to be in sequence or range and nothing about the cells being seperated. I also tried using the countif method, but same prob, the cells need to be within a horizontal or vertical range
Average From Several Averages
Column 1 contains the number of times an ad has shown
Column 2 contains the average position of the ad from column 1
Ad Impressions.........................Avg Position
Doing the following,
This is not the real average as this is directly influenced by the number of ad impressions...
How can i make a real calculation to give me the true total average position of my ads?
Averages With #N/A In Row/column
I am trying to work out the average volume of our daily downloads. I have used Vlookup to automate this. When there is no data it returns #N/A which is no problem. The problem arises when i try to work out the average for the month or peaks (MAX sum).
I have tried several variations of sumif/if/isna etc etc and am not having much luck. Below is an example of what i am trying to average out:
That is 4 different days download data - I want it to ignore the 0's and #N/A and return the average for the days we actually had a download.
Working Out Averages
I havea list of values that I need to work averages out for. Unfortunately, sometimes the values will be a zero and these should be ignored.
I can work out the averages easily enough using the average function, but can't quite figure out a way of ignoring the zeros. Sample data would be :-
week 1 - 3.42
week 2 - 0 (zero)
Using the standard average function, I get an average of 1.71 when I actually want it to have an average of 3.42.
I would prefer a function for this but would settle for a macro even though it would increase the program run-time.
If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.
Top/Bottom X Averages
Have a five column worksheet with the column headings run column A1 - E1 and there are currently 112 players filling out the data with many more to come
The 5 Column Headers are as follows:
Player# ... PlayerName ... M/F ... SessionAve ... GamesPlayed
What I need to do I pull out the top 5 male and top 5 female by SessionAve (including ties if possible). Would like the info to look like:
Male Shooter ...Session Ave ...GamesPlayed
Player1 .843% 30
Player2 .837% 35
Player3 .835% 25
Then Same for Female Players
Is This A Weighted Avg Formula
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.
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
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
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.
Weighted Average In VBA
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 ...
I have long lists of data which has name of manufacturer, number of items and fault rates of items.
Manufacturer.......Number of Items......Fault Rate
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
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
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
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.
Averages By Week By Month
I have the following data by week:
29-Mar 5-Apr 12-Apr 19-Apr 26-Apr 3-May 10-May
# of work days 4 4 4 4 5 5 5
Manpower 106 85 115 115 120 121 87
On a second tab, I want to show the data by month:
Apr May Jun
# of workdays 21 (4+4+4+4+5) 21 20
Avg. manpower 108 104 ....
Averages - Specific Characters
I need a formula that determines the average of numbers based on specific cells.
COLUMN A COLUMN B
John Doe 1.48
Jane X 2.89
John Doe 1.89
Jane X 5.98
Michelle Doe 5
Claire Doe 3
Jane X 0
What formula can I use to give me the average of "Jane X" not taking into consideration the one with the 0 Value?
Calculating Averages In Spreadsheet
I'm trying to Calculte the Averages of just the Teachers Salaries in my spreadsheet. If possible i would like to put the average at the end of each
of Teachers information.
Is there code that would allow me to do so.
I attached a sample file to view.
Variable Range Averages
I was able to record a macro that did essentially everything I wanted it to, with the important exception of getting the right selection for the average=() calculation.
My worksheets are set up with multiple variable columns, and multiple data collection stations as rows. The rows are sorted by date, with a blank row dividing the data by month. There may be 50 rows in January, then a blank row, then 4 rows in February, then a blank, then 15 rows in March, etc. I can easily average the monthly values in, say, column N by typing "average(" in the cell where I want the average to be calculated, then selecting the top cell for the month of interest in column N, holding the shift key down, pressing end, pressing the down arrow, and then typing ")".
The macro recorder, however, doesn't seem able to handle this method of selecting the cells to put inside the formula parentheses. If I try to run the created macro on other months, it will average the same number of cells targetted by the initial example (i.e. the month I was working with when recording the macro).
Is there any way to code this operation so it will vary the number of cells averaged, based on the number of data rows present in each monthly group? My research into coding averages is turning up only examples that deal with whole columns or fixed cell ranges.
Since I have a pretty big stack of worksheets to work though, it would be very nice to automate the process, but I'm still fumbling here. Not getting anywhere.
Getting Averages From Seperate Worksheets
The first one, I do i remove #DIV/0! from cell O7 in the worksheet "My overview"?
The second one, I need to get averages of the QA score for the 3 months from January to March in the worksheet "My Overview" Cell E14 for the corresponding person on each sheet. I understand that there need to be values in place to get an average but is there a way to overcome this?
Calculating Daily Averages
The data was taken in 15 min intervals and is organized by date. I have one column with the date and time and another column with the data. I need to find the average for each day. I have almost a years worth of data. Is there any formula I can enter to find the values in a given day and return the average of the values (without having to select the data for each day)? I want to be able to copy the formula down a column with the value per day.
Using Loops For Taking Averages
I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows:
startCell = ActiveCell.AddressLocal
stopCell = ActiveCell.AddressLocal
Now, clumsy programming aside, I just want to say something like this
averageCell = AVERAGE(startCell:stopCell)