calculated field

  1. V

    How to use a Pivot Table to sum the last 12 columns

    Hi Everyone, hope you can help....as usual. I have a pivot table based on an enormous database which essentially summarises each month points by individual. I need to keep all historical columns in the pivot (this is going back to 2012) but would like the pivot to calculate automatically the...
  2. T

    Pivot Table Calculated Field subtotals

    I am working on a pivot table based on data that has a region, a sub-region, dates, and then the available work potential in each sub-region by date. I need to do two things, 1. I need to create a calculated field based on the available work potential 2. I need to change the subtotals to...
  3. M

    Pivot Table Calculated Field Error

    I have a pivot table and one of the values I changed the value field setting to "AVERAGE" but the calculated field is calculating based off of sum. How can I get the calculated field to calculate off of the average? The Surplus Shortage field is my calculated field where I am taking Inventory...
  4. M

    Question calculated field pivot table

    Hello, I have a question. I would like to calculated ratio's from a database with balancesheets in a pivot-table. The data is as follows: <tbody> Company Balance Sheet no Description Value Year Apple 3 Cash 25 2018 Apple 4 Inventories 35 2018 Apple 5 Short Term debt 75 2018 Facebook 3...
  5. S

    Calculated Field in Pivot Tables

    Hello, I have the below fields in my spreadsheet that I would like to place in a pivot table. I want to calculate the sales turn for the aggregated with the formula (BOH $/Sales $)*# of weeks in the month. I have tried using an if statement in the calculated field =if(weeks in month>4, (BOH...
  6. J

    Pivot Table Difference From (or maybe something else)

    I'm trying to calculate the difference in TY vs LY numbers in a pivot table. My set up is: Rows: Client Name Sale Date (grouping by month) Columns: Years (for the TY vs LY comparison) Sum Values: Sum of Sales For example <tbody> 2018 2019 Client 1 May 100 1000 </tbody> I want to...
  7. H

    Calculated Fields Greyed out in Pivot Table with Data Model

    I watched the YouTube Video - Budget vs. Actual - Podcast 2016 where a data model is used to compare an actuals table to a pivot table, by using intermediary "Joiner tables." It's a great solution! I am trying to expand the idea by introducing a calculated column for the difference between...
  8. S

    Pivot table and divide one calculated column by another column

    <style></style>I have one column that counts all the document names on a certain date and sums the values on that day. I am wanting to add a column that will divide the Duration by the sum of the Doc Names on that day. I cannot get the calculated field to do the correct calculation. I am...
  9. J

    Using % of Parent Row Value in a Calculated Field

    I am looking to create a calculated field in my pivot table using the value generated from the 'percent of parent row'. I'd like to multiply it by another column but am finding that this might not be possible. Is there a way to do this? Or if not, is there an alternative? The pivot table has 4...
  10. P

    custom error bars in pivot chart based on calculated field

    Is it possible to create custom error bars in a pivot chart that are based on a calculated field? I would like to create a bar chart representing PROPORTIONS with error bars that represent the CONFIDENCE INTERVALS. My data contains N items that either can be "failed" (0) or "passed" (1)...
  11. D

    Need to incorporate popultation report into pivot table to calculate error rate

    I have an Excel 2010 workbook that includes the detail for a portion of the population of data. The portion was selected based on certain criteria. Research was completed to test compliance. The results are displayed in a pivot table. I need to calculate error rate based on a second report that...
  12. A

    Incorrect Count Subtotal in Pivot Table Calculated Field

    Hello, I have a report with a date slicer that returns, by sales manager, by sales rep, by account, the current period revenue and prior year revenue for the sliced date range. I need to add a column that counts the number of accounts with revenue in the current period. The data source is such...
  13. C

    PivotTable - calculation on Average data

    Hi Any help would be greatly appreciated as I'm a novice at pivot tables. I have created a pivot table to summarise a large data set . I have calculated the average size within the pivot table and a column that shows the max size allowed within spec. What I would like to do is to have a...
  14. A

    Create calculated field for pivot table, subtracting 2 following Qtrs

    hi all, This is the SQL data I imported into power pivot: <tbody> ProfitCenter_Name Account_Group Project_Group Fiscal_Year Q_FY Edition Edition_Type Revenue A AA AAA 2018 Q4/18 Actual Internal 5 A BB BBB 2019 Q1/19 Actual Internal 9 B CC CCC 2018 Q3/19 Other External 10 A AA AAA...
  15. M

    Pivot with both count distinct AND calculated fields

    Hey guys first post, good morning (well here anyway) to you all. I have looked for a solution to this and am hitting a bit of a blank, I am sure it must be answered somewhere Excel 2016. I want to add distinct count to a pivot table. in its simplest form, its sales rep, order counts, and...
  16. G

    Can a Pivot Table Calculated Field Return Text Values?

    I'm trying to use a Pivot Table Calculated field formula of IF(Column 50>20,"YES","NO"). It is only returning #VALUE . The formula works when I change the return values to numbers. Is it not possible to return text from a pivot table calculated field?
  17. J

    Finding Fraction of 2 calculated fields in Pivot Table

    I have a table which summaries a list of purchases made by customers. In this table it has 3 columns, the customer ID, their max budget and the purchase amount. I wish to make a pivot table which shows compiles the sum of purchases for each customer and the % of their budget used. I had no...
  18. T

    Pivot Table & Calculated Fields?

    Hi everyone, Apologies if my question has been posted or solved before, however I could not find so here I go. I have a series of HR headcount data, which contains various columns of 1's and 0's. I am trying to figure out turnover or attrition, but I would like to calculate this within a...
  19. N

    Formula for subtotal with conditions, in Pivot with Calculation - Fields (Pivot - Analyze - Calculation - Fields - subtotal by condition IF or SUMIF)

    This is Pivot table. Field1 is counted as Bottle price / Volume. But Subtotal for Field 1 is wrong. 21,2654321 is wrong answer, watching data, we understand that price of Bottle per Lt is 65. For some reason, Field 1 is taking all volume. Perfect Formula would look like this...
  20. R

    Pivot Table - Calculate values as a % of grand total from a different column

    I have tried a mix of calculated fields, and variations on showing values as % of rows/columns etc and cannot come up with a working solution. I would like to have a column that takes the "Orders with Cost increase" and shows it as a % of the grand total of the "POID count" For the first...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    • Replies: 44
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    • Replies: 35
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    • Replies: 28
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    • Replies: 24
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    • Replies: 23
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    • Replies: 23
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top