Index/Match fails when the Match Criteria is determined by a formula result

Hi Glen, Are you still in lock down?

what is the significance of 864000? For my conversion of time I use 24 * 60 * 60 = 86400. You have gone an order or magnitude higher? I will do some reading tomorrow about Floating point athematic to understand it better.

The reason I need the cell address of this "nearest time" value is because I can then use the Offset function to get the distance associated with that nearest time match.

From this I can then calculate speed in m/s and draw on a linear regression to adjust for overall time taken to travel the whole distance.

From there I can compare an estimate race time against an historical race record.

If the estimated race time is with a specified time difference from historical race record, then I get a green tick as part of the 24 analysis checks I make to accept the trade.

Is there a better way to get the value in a cell two columns to the left of the address of the nearest time value?

Chat soon.

Thanks Bo-Ry, I have to say I have spent several hours on this in the last two days. The time value issue sure causes me a drama.

It is late here in Oz, been a long day, I will be testing these out tomorrow, but for now, since both you are so knowledgeable in Excel, I will mark this thread as solved for now because I am certain that both of your solutions will see me smiling tremendously tomorrow.

Thanks Man.

Thanks Bo-Ry, I have to say that the formulas you and Glen wrote work perfectly with the Linear Regression. Thanks.

But to ask a silly question, when you use referencing like A4# or A4#,,1 etc in an array, I understand what it does, but what is that type of referencing called?

I want to learn more about it.

Cheers

Thanks Glen, this is a great solution, and works perfectly well with my linear regression. Now I also understand why I had so much problem with the floating point arithmetic.

Excel, really does not like time that much - hey?

cheers