Error when using Index(Match) to return time values

Question

I am building a table that calculates the time from a start point, and then returns the values each half hour from that start point of another variable.

My issue is that Index(Match) is returning a #N/A every third row.

My table I am looking up from contains dates correctly formatted in Excel time series style. My second table where I calculate the value to look up is also returning time series. When checked using IF([lookupTableTime]=[calculatedTime],"T") is returning true in every case.

The formula I am using to calculate the start time is:

=ReportDtNow+(ROWS($A$3:A3)/48)-1/48

Where ReportDtNow is a named cell returning a date (in this case 17/03/2013) at midnight.

The formula I am using as a look up is:

=INDEX(HHdata[Compressors '[DUMMY']],MATCH([@[Date this report]],HHdata[DateTime],0))

Where HHdata is my look up table, Compressors DUMMY is the value to be returned. Date this report is the calculated date to look up in the HHdata table in column [DateTime].

I've tried to modify to return non-exact values using the option argument in MATCH, though in the cells which return an #N/A error when MATCHed exactly, this just duplicates the value that preceeds it in the variable return column compressors.

Is Excel prone to some kind of rounding error in this case? Or is my formula off somewhere?

Answer 1

I've found a work around. Though honestly it's a bit awkward.

I've created a static series called Time by writing 00:00 in the top column then auto-filling down. By adding it to the start date in the next column I've successfully not only got rid of the #N/A every third row fora day, but also fixed the WHOLE column.

I had expected the column to simply auto-fill down as a repeated pattern of 48 half hourly intervals, then restart on the next day. What actually happens is at entry 49 it effectively ticks over to the next day, which is pretty neat.

Still doesn't quite explain what was wrong before, but it indicates that it was something to do with either the time series it was looking up, or the formula that calculated the progression from the first date. Does anyone know which?

Answer 2

Instead of =ReportDtNow+(ROWS($A$3:A3)/48)-1/48 please try:

=ReportDtNow+ROUND((ROWS($A$3:A3)/48)-1/48,15) 

(or =ReportDtNow+ROUND(((ROWS($A$3:A3)-1)/48),15))