How to convert 15 minute data into 10 minute data

Or, also using forecast

Formulas in Table 3 are

B2 =XLOOKUP($A2,Table 1::B,Table 1::B,"",−1)

C2 =XLOOKUP($A2,Table 1::B,Table 1::B,"",1)

D2 =XLOOKUP(B2,Table 1::$B,Table 1::$C)

E2 =XLOOKUP(C2,Table 1::$B,Table 1::$C)

F2 =IF($A2=B2,D2,ROUND(FORECAST($A2,D2:E2,B2:C2),2))

Fill down to complete the table

I added the IF statement around the FORECAST function because FORECAST kept giving me an error for 12:00. All other times worked. It said it "contains a number outside the valid range". I expected all that had the same "earlier time" and "later time" in the table would fail but they did not, just 12:00.

Here was my approach:

Working with dates and times can be painful sometimes, especially if you are using a date&time format that is not built into Numbers and if you are converting from/to text. If your data does not span multiple dates, it is easier to use just the "time" part of it. That is what I did below. I can post a version that converts your date column & time column to a Date&Time value then splits it up again at the end if you need that instead.

I copied your data from your post here, selected cell A2 in a blank Table 1 and Pasted to import the data

For column A of Table 2, I entered 12:00 into cell A2 and 12:05 into A3. I selected both cells and dragged to the bottom to complete the column. I then selected them all and changed the format to text. They should align on the left side when they become text.

Table 2 formulas are as follows

B2 =XLOOKUP(A2,Table 1::B,Table 1::C,"",0)

Fill down to complete the column

C2 =B2

C3 =ROUND(B2+(B5−B2)÷3,2)

C4 =ROUND(B2+2×(B5−B2)÷3,2)

Select C2 through C4 and drag down to complete the column. The pattern will be copied down.

Table 3 captures the 10min data

A2 =INDEX(Table 2::A,(ROW()−1)×2)

B2 =INDEX(Table 2::C,(ROW()−1)×2)

And here's a one-big-formula approach:

The formula in B2 of the 10-Minute table, filled down the column is:

=IFERROR(FORECAST(A2,OFFSET(15 min::B$1,XMATCH($A2,15 min::$A,−1),0,1+XMATCH($A2,15 min::$A,1)−XMATCH($A2,15 min::$A,−1),1),OFFSET(15 min::A$1,XMATCH($A2,15 min::$A,−1),0,1+XMATCH($A2,15 min::$A,1)−XMATCH($A2,15 min::$A,−1),1)),XLOOKUP(A2,15 min::A,15 min::B,"error"))

It uses the FORECAST function to interpolate (estimate along a linear trend). All those complicated looking OFFSETS are simply constructing the appropriate ranges in the columns to apply the FORECAST function.

As shown, it works with both numerical values (numbers of minutes) as the x values or date-time values.

It's reassuring that it produces the same values as Badunit's solution, which is easier to understand.😀

SG

Second thoughts…

Not all of the formula above is needed,

The IFERROR function and the first INDEX…MATCH part can be omitted, leaving only the latter two INDEX…MATCH parts, wrapped in AVERAGE.

For times which have no exact match in Original::A, the two MATCH functions will work as they do above, finding the largest 'Time' less than the time searched for and the smallest 'Time' greater than the time searched for, and returning the data values observed at these times.

AVERAGE will then calculate and return the average of the two data values returned.

For 'Times' which do have an exact match with the search value, both MATCH functions will return that matchng value, and each INDEX will return the value observed at that time. AVERAGE will return the average of tee two identical values, which, of course, will be the same as the value returned by each of the INDEX…MATCH functions (and the same value as would hae been returned by the first INDEX…MATCH function in the longer formula.

Regards,

Barry

If you need to keep "totals" equal, you can see what happens to the numbers if you true it up every 30 minutes, which is when the 15 min and 10 min data points coincide. Just add/subtract the necessary amount from those data points. Or the deficit/surplus can be determined at 30 minute intervals and applied equally to the previous two (10 min and 20min) previously calculated values. This second idea I believe will require more than two decimal places. A third idea would be to apply it equally to the previous two points when divisible by two, unequally when not.

You could assume a linear change between each pair of 10 minute data points, and calculate the 15 minute intervals using that assumption. :00 and :30 valies would be the same for both lists.

:15 and :45 values for the second would be the average of the values taken five minutes before and five minutes after the charted value time.

Here's are teo examples, the first graphing the original (observed) value set, the second displaying the observed values for :00 and :30 observations and the average of the two observed values bracketing the ;15 and :45 observations.

Formula for column B of the "15 min" table, entered in B2 and filled down to B14:

IFERROR here is used as a switch to choose which part of the formula to use.

In B2, (and other rows where the value in A matches one of the observation times listed in column A of Original, MATCH in the first line will find that value, return it's position to Index, and Index will return the value on the row of column B indicated by MATCH.

In B3 (and other rows where there is no matching value on the Original table, that formula will throw a 'can't find' error, and IFERROR will call the AVERAGE part of the formula to calculate the Average of the vlues returned by the second and third versions of the INDEX(MATCH) statement in that part.

The three MATCH statements differ only in what they will select as an acceptable value—an exact match, the largest value equal to or smaller than the searched value, or the smallest value greater than or equal to the searched value.

Regards,

Barry