![]() The first Production Total in your budget (reading from the top down) defines what is "Above-the-Line" and what is "Below-the-Line." You can add as many Production Total lines to your budget as you like and rename them as you see fit. Production Totals are subtotals that calculate the sum of all the Categories above them up to the previous Production Total or the top of the budget. Most of the rows on your Topsheet will be Categories, the top-level accounts (or headers) that contain multiple Accounts and roll-up the total costs for that group. You can stick a cell reference to its total under the visible portion of the pivot table, or even recreate your own Grand Total row and hide the one generated by the pivot table.Find & Replace: Amounts, Multipliers, or Rates You can label the columns more descriptively, but if you don't want the new column visible, hide it. The pivot table will then contain everything calculated both ways, so everything you need is there. Add another field that's the SUM of the new Rounded data column, as in my example. In the pivot table, keep your calculated rounding field since that displays the aggregations in the form you need. This is easy to maintain as you add or modify client records. The best way to accomplish what you need may be combining calculations internal and external to the pivot table.Īdd the Rounded calculation, like I did, as another data column that feeds the pivot table. But that won't necessarily break everything every time the data changes (although you will need to trigger recalculation of the pivot table).įrom the discussion, you have some complex requirements that benefit from the aggregation the pivot table offers, but don't lend themselves to doing it in a simple, straightforward way. If what you require is the aggregation shown in your example (aggregate, then round), you can't use a pivot table to spit out a total based on calculating it a different way. There are limitations to what you can do in a pivot table. To preserve some of the discussion in the comment thread: For pivot tables with any complexity, it's a good practice to double check the results to confirm that it's doing what you want. aggregating, especially when you apply calculated fields or even use aggregation functions that involve more complex calculations than simple summing or counting. Pivot tables are a fantastic tool for simple aggregations, but you need to be careful about the sequence in which Excel applies calculations vs. The column label in your pivot table would more accurately be "Rounded of Sum". ![]() So despite the column header label, the pivot table isn't performing the calculation you expect. What is happening in your case is that Excel is aggregating the raw income values from your data table, then applying the rounding calculation to the aggregate. If you need a calculation applied to each raw value prior to aggregating, do it outside of the pivot table. You can see that the pivot table correctly summarizes the data when you do it this way (which is the solution to getting the result you want). I had to create another column with the rounded values, and then include that column in the pivot table: I don't have access to a version of Excel that features inserting calculated fields, but I can demonstrate what is happening in your case (which you correctly identified in the question). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |