Track New Values
 

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.

Track New Values
 

=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

On Jul 23, 10:06 pm, bgetson wrote:
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.

Track New Values
 

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.