Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table formula (calculated field) result is incorrect
Underlying data has several attributes plus shares. It also uses a lookup
field to populate a price and date column. Looks like this (not all attributes listed): Account Dealer Branch Rep Fund AcctType Shares Price Date The pivot table essentially summarizes the data using all attributes listed above except Account (i.e. takes it up one level). Here's the catch. The pivot table is being used to replicate the results of a program for testing purposes. The program is rounding at a specific breakpoint - Dealer, Branch, Rep, Fund, AcctType - meaning that it is summarizing shares at the level indicated, then multiplying the results for that level by the price for the corresponding fund and rounding to 2 decimals to obtain a base record. All further program summarizations represent the addition of one or more of these base records - ensuring no further rounding occurs. Pivot table columns are therefo Dealer Branch Rep AcctType Fund Price 'Base Record Shares' where 'Base Record Shares' is the Sum of Shares for that level from the underlying data. I have added a Calculated Field as follows: Based Record Values = ROUND(Shares*Price,2) Since I was unable to do the price lookup in the formula, I added it as a column in the underlying data so i could use it in this formula. The formula is trying to replicate the rounded base value from the program - taking a share summary and multiplying it by the price and then rounding the result. Here are some sample values: Dealer Branch Rep Acct Type Fund Price Base Record Shares Base Record Value 1234 000 ABCD Rtmt 427 27.42 3,845.569 210,891.00 460 9.78 18,960.833 185,436.95 As you can see, the Base Record Value is twice the value it should be for fund 427 (3,845.569 x 27.42 = 105,445.50) and 5.61 times the value it should be for fund 460. This is consistent throughout the pivot table despite the number of accounts varying from 0 to 5 for any given summarization. In fact, in the example above, there are three accounts for fund 427 that should be included in the total (one with zero shares) and if I change the shares data field from sum to count it only shows 2 accounts and only includes the value of one. Actual correct value for the first record is: Shares = 5,727.907 Value = 157,059.20 This behavior is not consistent - some share summarizations are correct. Anyone have a clue what might be going on here? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table formula (calculated field) result is incorrect
Update:
After playing with the underlying data to ensure consistency in the values, I have fixed the Shares summarization problem - this data field now shows the correct count and sum. However, my problem remains with the calculated field for Value except that now the value is 3 times the correct value for fund 427 and 8.411 times the correct value for fund 460. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculated field in pivot table, simple Average formula | Excel Discussion (Misc queries) | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
Using a MIN, MAX formula on a calculated field in a pivot table | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) |