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?
=SUMPRODUCT(B2:B11,C2:C11)
View Complete Thread with Replies
Sponsored Links:
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? 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 Replies!
View Related
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...... Sub Results() Range("C9:E9").Select Range("E9").Activate Selection.Copy Range("C23").Select ActiveSheet.Paste Range("C9:E9").Select Range("E9").Activate Application.CutCopyMode = False Selection.ClearContents End Sub
View Replies!
View Related
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?
View Replies!
View Related
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 118 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,lowlow,low,highlow,high,highlow,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
View Replies!
View Related
Picks Up The Drifferences Between Low And High Numbers
picks up the drifferences between low and high numbers for example 110 is low 1129 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
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
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) Problem: 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)
View Replies!
View Related
Formula For 2 Low Net & 1 Low Gross Score In A 5 Man Golf Team
I am using Excel 2002 in Xp and also want to use the spreadsheet on my Pocket PC using Pocket Excel. I have a column of 10 numbers with rows 1,3,5,7 & 9 representing the individual gross scores of the 5 team members. Rows 2,4,6,8 & 10 have either a 0 or 1 (calculated) in them representing the strokes (or discount) the player receives for that hole. So the net scores for each player will be row 1 + row 2 and so on. I need a formula that will calculate the total of the 2 lowest net scores and 1 low gross score with the score for each player only being used once which will then represent the team score for that hole. A sample column would have 4,0,3,0,4,0,3,0,5,1 in it.
View Replies!
View Related
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.
View Replies!
View Related
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 ....
View Replies!
View Related
Calculate Averages Based On Where Names Are Positioned
Code:..... 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.
View Replies!
View Related
Calculate A Lot Of Averages Of Nonconnected Values
Situation: I need to calculate a lot of averages of nonconnected values. That is, they are separated by several rows (which also contain values). So far, I use the codesample 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 nonconnected 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 loopvalue 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............
View Replies!
View Related
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 %
View Replies!
View Related
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....
View Replies!
View Related
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, CloumnA CloumnB 1 (last value) 1 1 (last but one) 1.21 1 (2nd last) 1.2 . .etc . 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.........
View Replies!
View Related
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.
View Replies!
View Related
Sorting Hi To Low
how to create the database and the resulting Pivot Table. My Sales Database includes the Sales $ and GP$ for each line. In the Pivot Table I've managed to create a calculated field for GP %, no problem. i have also managed to figure out how to express each Row as a % of the Pivot Total, also very easy. What I need to know is how can I sort my Pivot Table High to Low using these calculated fields?
View Replies!
View Related
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 closing trades). This macro is included in the attached spreadsheet. It Is: ....
View Replies!
View Related
Low Single Value In A Column
I am looking for a formula to return the lowest value (MIN) in a range only IF it is the single lowest number. It's to determine a "skins" winner in golf, so I am looking for the best score on a hole that is not a tie, all other values can be zero.
View Replies!
View Related
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. Example 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 6671 and also bring the score down to row 6671 to show who won each event? example spreadsheet attached.
View Replies!
View Related
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 aaabbb, bbbccc, cccddd, and then aaaccc, bbbddd, and then aaaddd and so on. I know the VBA has a function to do repeated executions but am not sure how to adjust the time intervals.
View Replies!
View Related
Macros Dont Work Even On Low Security
My dad wrote me a VB programme to save me time entering repetitive data into an excel template for my business. At first it didnt work untilll i reduced security to low. Sice then I fried my computer and have had to reformat it with a diffirent version of windows, vista service pack 3. Now even on low security it doesnt work. The little button in the worksheet to make the macro happen isnt visible or doesnt exist.
View Replies!
View Related
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  =INDIRECT(ADDRESS(MATCH(J2,$A$1:$A$1400,0)+1,COLUMN(B1))) To find the closing price the formulae is  =INDIRECT(ADDRESS(MATCH($J$3,$A$1:$A$1400,0),COLUMN(E2))) 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.
View Replies!
View Related
Low Memory Launching Windows Explorer
how to launch windows explorer from a path that resides in a cell using the code below. Sub LaunchWindowsExplorer() Dim PID As Double Dim strRootPath As String Const strExpExe = "explorer.exe" Const strArg = " " '" /e,/root, " '// Change rootpath here strRootPath = "J:MyDirectoryWilliams,Bob 7264" PID = Shell(strExpExe & strArg & strRootPath, 1) End Sub The problem I have is that once I have done this a number of times (probably somewhere between 80100 times then I notice windows explorer starts to play up as if running out of memory. A reboot soon fixes that, however, I was wondering if there is anything I can add to the code to keep it from hogging up memory...if that is even possible. When I say 80  100 times, I don't mean opening up 80  100 instances of windows explorer. I mean launching windows explorer, moving some files then closing that instance of windows explorer. Then coming back into excel and repeating that process.
View Replies!
View Related
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)))
View Replies!
View Related
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 May 2007 Jun 2007 20 Jul 2007 0 Aug 2007 Sep 2007 10 Oct 2007 0 Nov 2007 10 Dec 2007 18 Jan 2008 8 Feb 2008 6 Mar 2008 0 Apr 2008 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.
View Replies!
View Related
Automatic Averages
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
View Replies!
View Related
Rolling Averages
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.
View Replies!
View Related
Averages Formula
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
View Replies!
View Related
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 Like so, Ad Impressions.........................Avg Position Google: 14,220,023..........................3.2 Yahoo: 346,656...............................1.2 MSN: 123,234..................................8.9 Total: 14,689,913............................??? Doing the following, = Average(B2,B3,B4) 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?
View Replies!
View Related
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 runtime.
View Replies!
View Related
Splitting Averages...
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.
View Replies!
View Related
Weighted Average
I have a large dataset that includes information on the investment payout ratios of US companies over the period 20042006. I need to calculate the weighted average investment ratio for each companysee 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 Replies!
View Related
Weighted Scoring
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.110 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.110) that will reflect how well they spent.
View Replies!
View Related
Weighted Average ...
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 Replies!
View Related
Averages By Week By Month
I have the following data by week:  29Mar 5Apr 12Apr 19Apr 26Apr 3May 10May # 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 .... 
View Replies!
View Related
Averages  Specific Characters
I need a formula that determines the average of numbers based on specific cells. For instance: 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?
View Replies!
View Related
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.
View Replies!
View Related
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.
View Replies!
View Related
