How do I fill missing data in the middle of a series?

Question

Level 1

0 points

How do I fill missing data in the middle of a series?

I want to fill missing data in a series of start times. I have a range of dates in one column and the time an event happens on that date in the next column. The start time changes to a few minutes later each day. In Excel for windows I could easily fill the missing times in a range. A simplified example:

Day 1 8:00

Day 2

Day 3

Day 4

Day 5

Day 6

Day 7 9:00

In Excel I could select the cells from 8:00 to 9:00, select fill series, and it would automatically calculate and fill the middle of the series. If the number of days between 8:00 and 9:00 were to increase, the incremental change in start times could be made easily using the same method. I believe in Numbers I could fill down and just define the increment myself (I think Numbers can do this with times? I don't know for certain), but because the number of days changes frequently, I want to avoid having to calculate the increments every time.

Numbers-OTHER, Mac OS X (10.7.4)

Posted on Jun 28, 2012 9:04 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Jun 29, 2012 1:05 AM in response to lizardpansy

Hi liz,

Here's one way to achieve a similar result. It uses two tables, one to hold the first Day and Last day starting times, the other to calculate the start times for each day in the series.

Here is the same pair of tables after another seven days have been added to Table 1 (left):

Here is the formula used in Table 1::B2, and filled down from there to the end of column B:

=Table 2::$B$2+(COUNT($A$2:$A2)-1)*(Table 2::$B$3-Table 2::$B$2)/(COUNT(A)-1)

Note that the dates in the second seven days are not consecuive. The formula sets the time increment by the number of occasions, not by the number of days between Day 1 and Day 14.

Note: Every cell in the two tables (except the two empty cells and the four containing text) contains a Date and Time value. If the First Day and Last day times are edited, it's essential that both be edited on the same calendar day (as determined by the Mac's system clock). Numbers includes the date the entry was made as part of the D&T value where only the time part was specified. If I had entered 8:00 AM yesterday, and revised the Last day time to 9:00 AM today, the difference between the two would be 25 hours, not 1 hour.

Regards,

Barry

Reply