Weighted Average Calculations for Pivot Tables

How does one implement weighted averages in pivot tables.

Assume

Bonds       Interest Rate
100,000       .06
200,000       .04
800,000       .10
Weighted Average Interest Rate is .0927
(I normally do this using sumproduct(col a,col b)/sum(col 
a) 

I would like to be able to perform this in a pivot table 
with the weighted average calc being a grand total.  You 
can see how this can be useful and expanded once the 
basics are found.  Any ideas.

ffrazz
0
ffrazz (1)
10/4/2003 1:55:34 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
554 Views

Similar Articles

[PageSpeed] 38

Reply:

Similar Artilces:

OT: demoralized tables
Kirk Kuykendall has a good overview on Common Table Expressions http://www.sqlservercentral.com/articles/CTE/62291/ but his conclusion contains a "new db term" (at least I had never seen it before)... "As you can see CTEs can be very useful. I have found them particularly handy when cleaning up demoralized tables. Like imported spread sheets." I assume it was on purpose and couldn't resist passing it on. We've all been there... gary I'm wondering if demoralized tables might be an undiscovered cause of file corruption. I would think that one table wit...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

9.0 Table Changes
Will be updating to V9, am looking for a list of the tables that changed and the documentation says it is on V9 cd 2, under the SDK But I don't see anything on the CD so do I have to install v9, and the SDK before I can get a list of the table names? -- Doug You need to install the SDK "Doug" wrote: > Will be updating to V9, am looking for a list of the tables that changed and > the documentation says it is on V9 cd 2, under the SDK > > But I don't see anything on the CD so do I have to install v9, and the SDK > before I can get a list of the tabl...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

partially turning off auto calculate?
Any way to NOT auto caluculate 1 particular formula, while leaving the rest of excel to auto calculate? In particular, I have an SQL query I would prefer to run manually, while not interferring with the rest of my workbook(s) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Calculation is an all or nothing setting. You can't prevent calculation of specific ranges. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com ...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss´┐Ż:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing tables in the CRM database and/or adding complete tables to the CRM database?? I am asking this from the point of view relating to other existing CRM products (saleslogix, frontrange - shh). From what I have played with so far from playing with my SBS2k/MSCRM install is I my need to have a MS developer skillset. Robb, Using the schema manager in the deployment Manager MMC snap-in, you can add fields to the existing tables. However, there is no way to add new tables. Matt "Robb D" <robbd@eproductscons...

How can I compare the column names from 2 tables and output them?
I have a requirement to place an alert if the field does not exist in my table, tblStaging. Can any one guide me to reframe this query with error handling messages? I know it is not good practice to use select * but I need to do this as my columns\field names change each time. INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails; Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1 Access is a relational database. If your "table design" has the fields in your table(s) changing frequently,...

Average ()
Friends, How do get Average of the values from 6 Different Fields fromsame row? [Not the avg of rows from the same field} Andy On Fri, 18 Jan 2008 14:28:00 -0800, Andy <Andy@discussions.microsoft.com> wrote: >Friends, >How do get Average of the values from 6 Different Fields fromsame row? [Not >the avg of rows from the same field} >Andy If none of them will ever be NULL it's easy: ([Field1] + [Field2] + [Field3] + [Field4] + [Field5] + [Field6]) / 6 If you want to ignore nulls, i.e. the average of 1, 2, 3, NULL, NULL, NULL should be 2: (NZ([Field1]) + NZ([Fiel...

Modify Calculation
When a user enters data into a cell, he has to tab out of it before he can push the "calculate" button. Is there any way to change this so that the calculation can be performed while the cell is still selected? I appreciate any help. Change Tools>Options>Edit>Move selection after enter, and use Enter to complete entry rather than Tab. This prevents the selection from moving to another cell. Note that calculation can never be done while the cell is being edited. "Sisilla" <sookdeoss@bowater.com> wrote in message news:0b4e01c34b9b$d59ebcf0$a301280a@p...

Problem Changing from Average Costing to FIFO Perpetual (Version 9
Our company changed our inventory valuation method from FIFO Perpetual to Average Costing. After two months we reverted back to FIFO Perpetual. We now have a problem when we create a new SOP document. The cost that the system is retrieving on the document is the CURRCOST from IV00101, not the STNDCOST as we would expect. We have run the Change valuation method utility and have checked links and reconciled, but this behaviour continues. If we create a new item that have not gone through this FIFO to AVG to FIFO chance cycle it picks up the STNDCOST correctly. Any ideas? Thanks, J...

Qry Running Average
I am really stuck. I have tried this a dozen different ways without results. I want to take a simple query (Query has date formatted to month and a summed quantity) and create a running average by 12 month groupings as well as extend out to 6 months beyond for running average forecast; as shown below: [The 12 Month Running Avg are my own calculations, this is where I am stuck] Part Nbr Month SumOfOrd Qty 12 Month Running Avg 51009 Mar-06 8 51009 Apr-06 1 51008 May-06 1 51008 Jun-07 0 51008 Jul-07 0 51008 Aug-06 3 51008 Sep-06 0 51008 Oct-06 12 51008 Nov-06 2 51008 Dec-06 7 51008 Jan...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

Table-lookup transformations
Hi, we are doing some table-lookup transformations based on LEFT and INNER joins. Probably around 100 - 50K rows/second or something like that. I have noticed that the lookups are getting slower and slower due to a growing database. The server itself is heavily loaded, CPU and IO-subsystem - which I know is not a good combination with SQL server :| I am looking for a way to increase the table-lookup transformation speed without mixing up things with SSIS.. Are there any good ways of doing table-lookup transformations within SQL server? Would it be a good idea to write s...

vbscript insert into access 2003 database with two different table
I am trying to insert data collected by WMI. Here is the script On Error Resume Next Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const ForReading = 1 'Create FSO Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an environment for the script to work Set wshshell = WScript.CreateObject("WScript.Shell") 'Connection to the database Set cnn = CreateObject("ADODB.Connection") 'Connection to a Recordset Set objRecordSet = CreateObject("ADODB.Recordset") 'Opens the Database ...

Pivot Table #46
Hello, I have pivot table based on data that identifies an item as "late" "late if not rec'd today" and "late if not shipped today". The totol lists the correct number from each category for a location but when I double click on that total I get a list of every entry for that location, not just the late etc. What am I doing wrong? When you double click on a total, does it generate a new worksheet? If yes, it is listing out all the data, which the total field is made up of. The other possibility is that you are unhiding a summary field. Govind. xgirl...

Pivot table total decimal places rounding
The totals in my pivot tables have too many decimal places. I total -$1,025.31 and $1,023.87 and the result is $1.43999999999994 in the total cell of the pivot table but I need just $1.44 I have formatted the table as currency and using 97. Pivot tables can lose their formatting if the data is refreshed. Reapply the formatting, and make sure that 'Preserve formatting' is checked (under PivotTable>Table Options). AM wrote: > The totals in my pivot tables have too many decimal places. > I total -$1,025.31 and $1,023.87 and the result is > $1.43999999999994 in the to...

Corrupt / Missing Table
OK, so trying to diagnose a series of annoying problems, I've found that we are actually missing the ActivityBase table all together. I don't know how it happened, when it happened or why, but more importantly is that its gone, and we're adding data to the database faster than I can keep up. Any ideas on how to recreate the database? We can't afford to role back nor loose data in a reinstall... TIA! I would contact MBS support and open a support ticket. The $245 for the incident will be well worth it down thee road as you may have a lot of other problems as well. -- ...

Changing the names of fields in tables after creating other object
I just leaned about the naming conventions after I have created my tables, reports and several queries. Can I change the name of fields in my tables (to remove the spaces and give them unique names ie not just last name but childlastname) without destroying the work I have done in queries, reports, and forms. Mary -- Positive Direction for Youth & Families, Inc. (www.pdfyinc.com) Possibly. If you are using a newer version of Access, say 2003 or 2007 AND you have Name Autocorrect, and all it's options, enabled, it MIGHT work. I found it somewhat buggy. Before you...

Calculate data on 2 different worksheets
I have a very large workbook and for efficiency reasons I have calculation set to manual. I need to calculate 2 different worksheets, but because they refer to each other, I have to calculate them a couple of times to make all of the formulas update correctly. I've tried naming a single range: Range3 = range1,range2 then I use Range("Range3").calculate but the result if only one iteration thru the formulas and the final result still needs one more calculation. Short of putting all the data on one sheet, is there anyway I can insure 2 separate worksheets are completel...

Pivot Table VBA Reference Book & Blank Cells
Hi Everyone, I have a Pivot Table in Access 2003 and need the blank/null cells on the data axis to display "0". This is easy to do in Excel but I have searched through all of the options in Access and cannot find it. What is the VBA code required to set this option? Can anyone suggest a good reference book or website for coding Pivot Tables with VBA? Many thanks, David ...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...

Create tables from xsd
I have some xsd files and I want to create tables from them. Is there a utility to do this. I have used the xsd.exe to create the classes from the xsd files and that worked. I am new to xml programming. Thank you -- Jerry Jerry C wrote: > I have some xsd files and I want to create tables from them. Is there a > utility to do this. I have used the xsd.exe to create the classes from the > xsd files and that worked. I am new to xml programming. Do you want to create data base tables? If so which data base management system do you target? Within the .NET framework class library ...