Variable cell reference based on minimum difference

**smokeywa** · 02-12-2010, 03:40 AM

Hello,

I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.

Can this be done with worksheet formulas?

Thanks for any help.

Harald

**zbor** · 02-12-2010, 03:44 AM

Can you upload example workbook please?

**DonkeyOte** · 02-12-2010, 04:00 AM

You can use LOOKUP construct to find the last value that meets criteria, eg:

C20: =IF(COUNTIF($A$1:$A19,"<="&$A20-1),$B20-LOOKUP(2,1/($A$1:$A19<=($A20-1)),$B$1:$B19),"")

the COUNTIF is to return Null where no valid data points exist in the prior set

**smokeywa** · 02-12-2010, 05:00 AM

Thanks DonkeyOte, works like a charm.

Took me a while though to get my head wrapped around how the lookup works.

So in addition to Excel help where it says "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value", it also picks the last one of largest ones in case of a tie, i.e the last one of all the 1s in this case.

**DonkeyOte** · 02-12-2010, 05:10 AM

In this context it's a little confusing... I (like others) have tried outlining the basic principles before and I'll add a link but in general...

LOOKUP assumes all values in the lookup_vector are sorted in ascending order (irrespective of reality)

LOOKUP returns last value <= criteria in the lookup_vector (or associated value from result_vector if specified)

LOOKUP ignores all values in the lookup_vector that are not of the same data type as the criteria - this (importantly) includes error values
ie error values in the lookup_vector won't cause the LOOKUP to fail.

So in this context each value within the lookup_vector can be only one of two things:

1 and #DIV/0

1 where conditions have held true - eg 1/TRUE -> 1/1 -> 1
#DIV/0 where conditions have not held true - 1/FALSE -> 1/0 -> #DIV/0!

Given

a) the criteria value is numeric (2) only the 1's are reviewed by the LOOKUP ... all #DIV/0! entries will be ignored given they are of a different data type to the criteria itself

b) LOOKUP assumes the last value found must be the biggest (assumes ordered) and given this must be less than our criteria value (2 > 1) it will return that value

c) we have a result_vector

this means that instead of returning the last "1" in the lookup_vector it returns the value associated with that value from the result_vector

link to prior waffle: http://www.excelforum.com/2100732-post14.html