0
I have flow measurements per 15 minutes. There are days with no data. I have used pivot table to get the daily average of flow. How can I convert pivot results into continuous list of days as illustrated below?
0
I have flow measurements per 15 minutes. There are days with no data. I have used pivot table to get the daily average of flow. How can I convert pivot results into continuous list of days as illustrated below?
0
You can create a pivot table, drag the dates into the row panel. and the numeric values into the values panel.
You can then click on any date in the pivot table and group. You may want to group by Year, Month and Day.
Be aware, though, that a pivot table only shows existing data. That means that if your source data table has values for Jan-1 and Jan-3 but not for Jan-2, then the pivot table will not list Jan-2 at all.
If you want all calendar days to be listed, you may need to ensure that your source data contains at least one data point for each calendar day. You may need to add missing days to the source data manually.
Pivot tables do not interpolate missing days.
If you want to create a new table, based on the pivot table, you can use formulas to look up the actual date value and return the number (a simple Vlookup() will work). If the Vlookup in the row for Jan-2 returns an error, since the pivot table does not contain a value for that day, you can wrap the Vlookup in an IFError function and provide in the second argument what to return in an error situation.
What exactly that needs to be is up to you. You need to decide the business logic first and put it into words. Then a formula can be found. For example, you can return the previous row's value, or an average of the last ten rows, or a static value that lives in cell ZZ333.
Edit: You can avoid the grouping if the source data has only dates, not dates with times. To achieve that, add a column to the source data and work out only the date of the current date/time value. The formula for that is =int(A1)
. Use that date-only column in the pivot table and you don't need to group.
Many thanks for your detailed explanation. could you please explain in more detail how the vlookup will work while the days in the pivot table have no year. I tried to use vlookup but it didn't work https://www.dropbox.com/s/modm96g0rnuctcy/vlookup.jpg. I think in order for the vlookup table to work, the days in the pivot table should be similar to the vlookup value (for example 1-Jan-2012)
– None – 2014-01-19T07:50:39.863To use a Vlookup, you need to ungroup the pivot table, so there is a proper date to find. On the other hand, once you ungroup the pivot table, you only need to add the missing dates to the source data with 0 values and then you don't need a second table via Vlookup at all. – teylyn – 2014-01-20T07:17:32.233
Thanks. However, the data are for each 15 min. If I ungroup the pivot table, I'll get the measurements for each 15 minutes. – None – 2014-01-22T08:17:05.450
1@aelwan, I edited my answer with a suggestion. – teylyn – 2014-01-22T08:40:16.177