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

View 5 Replies


ADVERTISEMENT

Optimizing Weighted Averages?

May 21, 2014

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.

View 2 Replies View Related

Weighted Averages Using SUMIF And COUNTIF

Jul 6, 2009

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 4 Replies View Related

Calculate Weighted Averages Using High, Medium, Low

Dec 31, 2008

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 7 Replies View Related

Weighted Averages - Accurate Productivity Percentage

Feb 20, 2012

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?

View 5 Replies View Related

Calculate Final Grades With Weighted Averages?

Mar 19, 2013

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 Related

Splitting Data By Sorted Ranges

Oct 24, 2008

How do I split a worksheet by sorted ranges and then save each range as a new workbook?

For example:

How would I split the data on the left into the ranges on the right of the image below?

View 9 Replies View Related

Calculate Differently Based On Value

Nov 26, 2008

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.

View 3 Replies View Related

If Negative Number Display It Differently

Jun 8, 2009

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.

View 9 Replies View Related

Merging Data From Differently-shaped Streams

Nov 19, 2012

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

Source A looks like this:

DateMeasured in00:0000:3001:0001:3002:00
28-Oct-2012KWH1091087
29-Oct-2012KWH77878
30-Oct-2012KWH108877
31-Oct-2012KWH99877

Source B looks like this:

Time<More columns> kW_Raw_Data

28/10/2012 00:04<More columns>194.541667
28/10/2012 00:05<More columns>180.32
28/10/2012 00:06<More columns>156.17
28/10/2012 00:07<More columns>157.243333

View 5 Replies View Related

Pulling Data From Differently Written Names

Nov 4, 2013

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%

View 2 Replies View Related

Macro To Act Differently On Selected Multiple Columns?

Jan 18, 2014

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.

View 14 Replies View Related

Worksheet Change Macro Runs Differently

Dec 3, 2008

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

End If

Next a

View 9 Replies View Related

Excel 2010 :: Sorting Different Sections Of The Same Worksheet Differently?

Aug 14, 2013

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.

View 2 Replies View Related

Excel 2003 :: Rank With 4 Criteria That Are Ranked Differently?

Nov 20, 2012

How can I rank with 4 criteria that are ranked differently? (Greater/ lesser etc)

Ranking - Online Spreadsheets - EditGrid

I had a look into this, but I got a bit confused with how isnumber, big, small and rank actually work

Using Excel 2003.

View 2 Replies View Related

How To Change Filepath Or Dir When Users Have Drive Mapped Differently

Oct 23, 2013

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

[Code]....

View 2 Replies View Related

Combination Chart: Present Source Data Differently

Sep 27, 2006

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?

View 6 Replies View Related

Publishing From Excel Document To PDF Produces Differently Sized Pages

Feb 13, 2014

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?

View 4 Replies View Related

Excel 2007 :: Column Widths Display Differently On Different Computers

Apr 11, 2013

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.

View 3 Replies View Related

Move Differently Named Sheets In Workbook - Some Missing Due To No Info

Apr 18, 2013

I am trying to use a macro to move a range of excel tabs to the front of the workbook however some of the tabs could be missing due to no information.

If I simply record the macro by moving them manually if the procedure arrives at one that is missing it would report an error.

So in essence I need it to look for the tab and if its there move and if its not move onto the next until it arrives at the last one.

View 9 Replies View Related

Format Formulas, Constants, Numbers & Text Cells Differently

May 8, 2008

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.

View 6 Replies View Related

Graph Appears Differently Between Normal View And Page Break Preview?

Aug 5, 2014

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.

View 2 Replies View Related

Shared File Prints Differently For Different Users - Autofit Wrapped Cells

Apr 16, 2014

Here's the details:

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

View 1 Replies View Related

How To Know In Which Order It Is Sorted

Mar 29, 2009

When it is sorted by ascending and next time when they click header I want it to be sorted by descending.

Right now I have implemented to sort Descending . Now how will I check in which order does it sorted?

If know the order in which it is sorted currently then I will sort it in the opposite way..

View 6 Replies View Related

Top 10% Of Sorted Data

Mar 5, 2013

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.

View 9 Replies View Related

Sorted List

Sep 19, 2006

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.

View 8 Replies View Related

How To Names Associated With Value Cells When Sorted

Apr 21, 2013

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.

View 7 Replies View Related

Sorted Data Class

Jan 8, 2010

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?

View 6 Replies View Related

How To Run Macro After Worksheet Sorted

Oct 27, 2011

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.

View 1 Replies View Related

VBA To Get Data Sorted Into Groups

Sep 20, 2013

Explaination of Data:

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

View 1 Replies View Related







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