Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Summaries
Hello,
I have created a pivottable to summarize production data for a two shift 6 day/week operation. I want to summarize Week-to-date and Month-to-date. I set up days as rows and shifts as columns. Days that have no data show up as errors due to division (as in by 0 hours). The table calculates daily subtotals, and subtotals for each shift, but for some reasons, it gives an error for the grand totals. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Summaries
Is the division by hours in the underlying data or in the pivot table itself?
If it is in the underlying data then you need to change the calculated field to an IF statement that include an ISERR statement to look for errors, e.g.: IF(ISERR(calculation)=true,calculation,0) So if ISNA sees and error it will return 0, otherwise it will return your normal info and this will stop the errors in your pivot. Hope this helps, Nigel "Rayo K" wrote: Hello, I have created a pivottable to summarize production data for a two shift 6 day/week operation. I want to summarize Week-to-date and Month-to-date. I set up days as rows and shifts as columns. Days that have no data show up as errors due to division (as in by 0 hours). The table calculates daily subtotals, and subtotals for each shift, but for some reasons, it gives an error for the grand totals. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Summaries
If the error is in the pivot table itself..... Choose Pivot Table / Table
options from the pivot table toolbar. Then check the "For error values show" box. You can place a zero in the box, leave it blank, whatever you want. The DIV0 errors you are now receiving in the pivot table should now go away. P.S. check you post from yesterday to get what I think may be your answer for the week number carrying over from year to year. Hope this helps. Thanks, Bill Horton "Rayo K" wrote: Hello, I have created a pivottable to summarize production data for a two shift 6 day/week operation. I want to summarize Week-to-date and Month-to-date. I set up days as rows and shifts as columns. Days that have no data show up as errors due to division (as in by 0 hours). The table calculates daily subtotals, and subtotals for each shift, but for some reasons, it gives an error for the grand totals. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |