0
I've a lot of data I must analyse but some data is wrong. That gives a problem for draw my pivot charts and tables. If there were no errors the chart must look like this:
But it doesn't. Here is what I get:
Of course the wrong fields are more then only that. In total I have more than 300k rows correct or not. I've no idea how many percent is wrong.
Update one: The data I've get was taken every 5 minutes over a period of 5 years. Sometimes is it possible that there is no data on the 5th minute but on the 6th or 4th minute. Well that mistakes must be filtered.
Update two: Here is some fake data from my pivot table where you can see that the strike through lines are wrong:
╔═══════════╦════════╗ ║ Time ║ kWh ║ ╠═══════════╬════════╣ ║ 07:40:15 ║ 32,65 ║ ║ 07:45:25 ║ 40,83 ║║ 07:46:05 ║ 0,00 ║║ 07:50:57 ║ 50,38 ║ ║ 07:55:14 ║ 55,71 ║║ 07:56:20 ║ 0,00 ║║ 08:00:14 ║ 60,72 ║ ║ 08:05:25 ║ 61,42 ║ ║ 08:10:54 ║ 70,78 ║ ║ 08:15:10 ║ 80,75 ║║ 08:16:01 ║ 0,00 ║║ 08:20:25 ║ 85,06 ║ ║ 08:25:02 ║ 85,46 ║ ║ 08:30:10 ║ 100,73 ║ ║ 08:35:25 ║ 100,21 ║║ 08:37:10 ║ 0,00 ║║ 08:40:47 ║ 115,60 ║ ║ 08:45:45 ║ 120,16 ║ ║ 08:50:01 ║ 128,77 ║ ║ 08:55:25 ║ 140,75 ║ ╚═══════════╩════════╝
Background information: the information goes about solar panels and the production in kilo watt hour of it per 5 minutes.
My questions are now:
- How can I filter that wrong data explained above out of my pivot tables and pivot charts in Excel version 2013?
- Can I calculate how many percent is wrong?
2Without looking at least part of your data. Such as "wrong" data doesn't exist as filter, you need to understand your numbers and tell what criteria makes a number to be wrong. Please edit your question to add more information. If difficulty for you is finding the criteria, then statistics stackexchange is probably more appropriate for your question. – Máté Juhász – 2015-12-19T11:10:34.703
@MátéJuhász: I've updated my question – H. Pauwelyn – 2015-12-19T12:56:12.293