Weighted Averages Across 2 Differently Sorted Ranges
Aug 16, 2014
I am looking for a way to get a weighted average to work without the need of helper columns, when my weights are stored in one range, and the actual values are stored in another range, but might not be sorted in the same fashion.
For example; range 1 might be:
Weight
Homework 120%
Pre-Exam 30%
Exam 50%
But the range for student's scores might be in a slightly different order:
Scores
Exam 90
Pre-Exam 72
Homework 1 81
(Note that this is a greatly simplified example and that just resorting would be impractical).
I 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.
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)
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?
I have a spreadsheet that tracks productivity for various parts being made basically pcs made per hour against how many pcs should be made per hour shown in a percentage of 100 percent
For example: Part ABC - ran 450 pcs per hour. The rate is 9500 so it ran at 4.74% of the goal
The machine runs about 50 parts.
They had me create a sheet/chart to graph productivity. They told me to just take an average of all the percentage rates the individual parts are running at.
I believe this is where a 'weighted average' should come into play? Taking an average of all those averages is not going to give an accurate overall productivity percentage is it?
How would I go about making a 'weighted' average (if that is indeed necessary) to get an accurate productivity percentage?
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.
I'm selling a product to clients. The first one is $20, and every subsequent one is $10. If they don't order any, I need the cell to read $0. I've been trying to get the IF function to work, and can't quite make it work correctly.
I currently have the following in a cell: =BDH(AHD!A3,"px_last","-5cm","-5cm")
This currently displays as -32 pulling this number out of bloomberg.
I now want to write a formulae in this cell that will display this as 32c if it is negative... and 32b if it is positive.
So if the number pulled out is negative I want it to have a c after it (and please have it delete the minus sign) and if positive, then have a b after it.
I have two sources of data (of electricity use) that I need to merge to plot detailed consumption. (Source B below needs to have calcs done on it and then be subtracted from Source A. Trust me, it just does). But their formats are transposed relative to one another, and one takes readings every half hour and the other once a minute. Plus, I'll need to append to the data periodically as more gets generated.
I'd describe my Excel level as "novice plus", i.e. IT- and database-literate but as far as Excel's concerned still trying to fully master pivot tables...
I have a spreadsheet with numerous transactions that contain the same name but are often spelled differently or inconsistently. I would like to pull all the transactions with the similar name and in a new sheet, make all the inconsistent ones, the same e.g. (space)mr smith or mr smiths or mr smith's or mr smith would all be mr smith. I am thinking of using the match command as a possibility but want it to repeat each row.
I am attaching a spreadsheet to show the existing table and the desired result which is similar to what I am showing below.
Example:
Existing Names and data A B C D E 1mr smiths xyz10020% 2 mr smith abc12430% 3mr smith's def20040% 4mr smith xyz7550% 5simon inc zbc14065% 6simon's inc xyz20070% 7simon abc9525% 8shows zbd18045%
Want it to show Names and data:
A B C D E 1mr smithxyz10020% 2mr smithabc12430% 3mr smithdef20040% 4mr smithxyz7550% 5simon inczbc14065% 6simon incxyz20070% 7simon incabc9525% 8showszbd18045%
I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.
I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...
So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..
I made my own small code but dont know how to make it act differently on selected columns....
I would select the entire matrix and then want the macro to run differently, taking into consideration the column names
[Code].....
I also need to add a vlookup formula for some column based on the column B's value...
Ex: This is for Col C
[Code] .....
So how do I write the code in such a way that the code acts on its own differently.
When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?
Original Sub sessions() Dim a As Integer Dim x As Long x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
Sheets(1).Unprotect Password:="password"
For a = 2 To x
If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then Cells(a, "f").Resize(1, 6).Select Selection.Locked = True ElseIf Cells(a, "m") Cells(a, "d") Then Cells(a, "f").Resize(1, 6).Select Selection.Locked = False
I am using Excel 2010 64-bit (with SP2 and all other updates installed).
I have a worksheet where some cells have a yellow background, based on conditional formatting of what is in column A. (Note that data in column A changes, so the row can sometimes be yellow and sometimes be white.)
I need to sort this worksheet as follows:
1. By background color, so yellow is on top, white is on bottom. 2. Then, within the yellow rows, data in column J. 3. Then, within the white rows, data in column A.
Currently, I do this manually by sorting the entire worksheet by column A, then selecting only those rows that are yellow and sorting by column J.
Is there a way to combine these steps, perhaps with a macro?
I initially thought to make each "background color" its own table, but since column A can change and thus the background color of a row can change, I need to be able to have rows move from yellow section to white section. I don't think having two separate tables allows that.
I have written code that allows the user to open a file. To make things easier I changed the file path to point directly to the folder where all the needed files will be placed. The problem is everyone that will be using this work book map their drives differently. One user may have it as H, and the other as J. Also users can have the same drive mapped but at two different starting points. Ex: H:DataReportsxxxyyy or H:Reportsxxxxyyy this causes the code to crash. Is there a way do default the path so it will not matter how the drives are mapped?
Here is what I am using so far:
Dim filt As Variant ' flit to be used in the getopenfile. variant is need for its multiple Dim FilterIndex As Integer Dim Title As String
I've taken over a spreadsheet and been asked to produce various charts. One chart in particular asks for views of the following (hyperthetical) data from different workbooks on the same sheet
July 06 - 24% June 06 - 22% May 06 - 29% April 06 - 21%
overlaid with this data
April 06 - 42 May 06 - 68 June 06 - 47 July 06 - 55
The problem I'm having is that firstly, the dates are presented the opposite way round, and secondly one set of data is in percentages, the other in basic integers. The spreadsheet data is large, and historically has always been done like this so it's not easy to change the way its presented, but is there an easy way to show it in a combination chart?
When I publishing from Excel document to PDF it produces differently sized pages as shown in attached. Is there a way I can achieve a same size page view without having to change the font size and page layout of the source Excel document?
Scenario: Two computers running Windows 7 (64-bit Professional) with Excel 2007, same processor, same hard drive, same memory, same everything except the monitors - see below
Computer 1: HP LA2006x monitor (20" viewable diagonal) @ 1600x900 resolution, 91.79 PPI Computer 2: HP LA2206x monitor (21.5" viewable diagonal) @ 1920x1080 resolution, 102.46 PPI
We have an Excel spreadsheet that the column widths on columns A through K must equal exactly 6, 1, 6, 1, 8, 1, 13, 2, 8, 1, and 35 respectively in order for the data to be imported into another program. These exact column widths are set on Computer 1 and the .xslx file is saved to a network location.
Computer 2 opens the .xslx file from the exact same network location. The column widths display as 5.86, 0.92, 5.86, 0.92, 12.84; not the exact columns listed above. So if anyone needs to export the data from Computer 2, they have to manually adjust the column widths or run a macro to auto adjust the column widths.
My question, is there anything I can do on Computer 1 or Computer 2 to make it so the column widths are saved and open correctly no matter which one is saving and which one is opening? The 20" monitor on computer 1 cannot display the 1920x1080 resolution... already tried that.
I am attempting to write a VBA function that will add textual formatting to each cell that has content. I am just learning VBA; so far, as an example I have
Sub NewSub() i = 1 j = 1 Do Cells(i, j) = "'" & Range(i.j).Value & "'," i = i + 1 Loop Until i = 40 End Sub
but, as I'm sure you'll notice, it does not work. In essence I want to wrap quotes and follow with a comma each cell that contains any content. The actual content test I haven't gotten to yet.
I printed my graph after editing it while in normal view. It did not appear on paper like it looked on the screen. I switched to page break preview to see if I could see the problem and noticed that the printed version matched the page break version. I am using text boxes for some of my data on the graph itself, and the boxes, moreso than the bars, appear differently between views. This has never happened before, and I print this graph every month after changing the parameters and data. To my knowledge, I did not change anything out of the ordinary.
Same file shared by many users in the office. File is maybe 2200 rows long and 20 columns wide. Last column is a text field where users can explain variances. Cells in the last column are wrapped and the rows auto-fit on the height. Zoom is set at 100%.
One person opens the file and it looks fine on the screen. Print preview also looks fine. File prints out perfectly.
Another user opens the file and it looks fine on the screen. BUT, print preview shows that the bottom line or lines of text is cut off. File prints exactly like the preview showed with clipped text.
Both users can point the file to the same printer, but that doesn't seem to change anything. Still prints with text clipped for one person and perfectly for another...
I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.
I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.
I have a list of entries on one sheet. This sheet contains part numbers in random order. I would like to draw out the part numbers onto a new sheet, with no repeats and in numerical order. I want this to be done automatically without using a sort command. Also, I would like to avoid VBA if possible.
I have an issue sorting values. i can get the numbers together and sorted, but want to keep a name associated with the values being sorted. so when the values are sorted, and they change the name associated with each value moves with it. Its acutally a first-tenth place in a nba fantasy league.
Does anyone know of an Excel object class that you can put objects into with a key and the process it in key-sorted order? If not, is there a library that I can reference to get such a thing?
Is there a way to run a macro after a worksheet is sorted (after sort event)?
Scenario: I have a several hyperlinks that use defined names to jump to locations throughout a worksheet. When the sheet is sorted the defined names don't update to the new cell locations (Microsoft KB 58245). I've written a subroutine that fixes the defined names but I don't know how to have the fix run automatically.
I prefer not to use Worksheet_Change without other conditions to prevent a lot of unnecessary calls to repair subroutine.
I have 2 Columns in the "RawData" sheet in the file attached. One says the Campaign No, and the other says the Site Code. One Campaign, will always never have duplicate Site Code.
I want to organize the data in the following fashion (as seen in the "Format" Sheet). I wish to acquire the trends of grouping of Site Codes, meaning which site (by Site code) is sold together, to understand the hottest combinations.
The idea is to see which Site Code sells more with a particular Campaign.
As you can see in the attached file, I'd like to know how many times a particular Sites (by Site Code) was sold with the other. As you can imagine, I have about 300 Campaigns, but have about 1500 different Sites codes to deal with, this activity will save me hours of time.
The sample file is uploaded on Google Docs. [URL] .........