Unable to get my pivot table to work properly

[hamohd70]

I have an excel sheet that with a sheets titled "Records" that I use for data entry and can calculate Simultaneous Machines Outage for me based on my selection.
To be able to generate chart for this data I thought of creating a pivot table in "SimultaneousOutage". While experimenting, I found that the result returned by the pivot table does not match the one calulated in the "Records" sheet.

I'm attaching the spreadsheet for your reference and help please.

[JeteMc]

Appears that there are two issues.
1) The range in records H5 is $M8:$M1513 as opposed to $M8:$M1539
2) In the first pivot table 'Overlaps' include empty, although instructions state exclude empty and zeros.
2a) Similar situation in the second pivot table with the 'Overlap' filter.
Let us know if you have any questions.

[hamohd70]

Thanks for the reply.

the Data source for both tables are set to "table2". The two tables have different set up for demonstration only.

My point is as follows:

1. go to records sheets and choose 4K-001 and 4K-002 as Machine 1 and Machine 2 in the "Simultaneous Machines Outage" area. You will notice that "Total HRS of Simultaneous Outage" is 462.26 and "Total Simultaneous Outage" is 48.

2. now go to the pivot table sheets and choose the same machines in any of the tables. you will notice that the "Sum of overlaps" return 0's. Sometimes after refreshing all, we may get the correct answer but sometimes we won't.

Try it again with CS-1 and CS-3. Try it with different combinations the "Sum of overlaps" return 0's most of the time.

Thanks again

[oeldere]

before I do further investigation:

are there merged cells in the data?

[hamohd70]

before I do further investigation:

are there merged cells in the data?

No merged cells.

[JeteMc]

I chose 4K-001 and 4K-002, as well as unselecting 5K-001 and 5K-001, on the 'Records' sheet and got the values you posted. After going through those steps in the pivot tables, refreshing and unselecting empty again, I got 462.26 and 48 there also. Sorry not to be of any more help.

[sandy666]

Maybe I misunderstood post#1 but...
If you correct ranges like JeteMc said in post#2 point 1, everything works well, both pivots and excel table
I tried different ways to get "error" but no success

[hamohd70]

Finally got it.

I do the overlap calculation in the records sheets based on machines selection; which means that the overlap column will only be filled for those machines set as criteria.

pivot table has be set to the same machine criteria to get the right answer otherwise it only give wrong answer.

So for it to work, I need to select the machines and fet filters in the records sheet first then apply the same in the pivot table. Then only ot will work.


Thank you all for the help and support.

[sandy666]

.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.