Track New Values

[bgetson]

I have a table of data that tracks progress across time (down each
row), so that one column (B) will hold a dollar value that will
change. This value is going to serve as a benchmark, so that it is
persistant for some time, then changes as it has been met.

My problem is that I need some way to track these benchmarks as they
change so that I can easily refer to the last one that was populated.
If at some point in my table, I have values:

Row Col. B
..... .....
20 $5000
21 $5000
22 $5000
23 $5625

I want the value $5000 to be available in rows 23 and down. Is there
an easy way to do this? I'm afraid of complex worksheet or array
formulas, because I have so many rows of data (104,000+).

Any help would be great. Thanks in advance.

 

[David Hilberg]

=SMALL($A$2:$A2,ROWS($A$2:$A2)-COUNTIF($A$2:$A2,$A2))

This assumes your benchmarks are in ascending order.

Assuming your data starts in row two, enter the formula in row 2 of
another column and copy down. It will show #NUM! for the first set of
(nonincremented) benchmarks, but once there is a previous benchmark,
will show that.

I know this is more cumbersome than you would like. An alternative for
you might be a macro / custom function to use only as necessary.

- David

 

[bgetson]

David, thanks for the help. From an initial look, this looks like it
would do the job, but it's just taking too much time to process. I
think by the time it reaches the upper thousands, it's taking too much
time to hold thousands of unnecessary values in memory.

I've managed to keep calculations to a manageable 7 seconds so far, so
I'm going to keep searching around for alternatives. Maybe a custom
function will let me only look as far back as needed to find the next
number.

Thanks again.