automatically update value in formula to last entry

Topic

New Reply

How do I incorporate the return of the vlookup function
=VLOOKUP(9.99999999999999E+307,A:A,1)

with the formula:
=IF(TODAY()-A7>0,TODAY()-A7,”0″)

where the VLOOKUP return will replace A7 in above formula with the last entry value
in “B”.

In other words, if I enter 3/20/2011 in “A8“, I want the “A7” in =IF(TODAY()-A7>0,TODAY()-A7,”0″)
to be updated to value of “A8” or 3/20/2011
and then the return of “D3” would be
=IF(TODAY()-A8>0,TODAY()-A8,”0″)

——
What advantage would the following approach in determining value of last entry have, if any.

=INDIRECT(“A”&MAX(ROW(1:65535)*(A1:B65535″”)))

Reply | Quote

Replies

=MAX(0,TODAY()-LOOKUP(9E+300,A:A))

Avoid INDIRECT generally as it’s a volatile function. Not usually a big deal if you only have one or two such formulas, but a good habit to get into, IMO.

Reply | Quote

Rory,
As usual, works as needed. Thanks.

How is 9E+300 used and mean? I have seen it’s use but I cannot find it’s value or meaning. My understanding is the value located here needs to represent a number larger than any it will find in its search so that the formula will settle on the last entry, correct?
But what does it represent and how?
How does it differ from: (9.99999999999999E+307
Why use one over the other?

Reply | Quote

9E+300 = 9 * 10 ^ 300 which is a very large number, certainly larger than any date you’ll be using! 9.99999999E+307 is larger still, but I’m too lazy to type all that!

Reply | Quote

What does the “MAX(0” do in
=MAX(0,TODAY()-LOOKUP(9E+300,A:A))

Why not “=(TODAY()-LOOKUP(9E+300,A:A))” ?

Reply | Quote

It replaces the IF part of your formula – in other words if the lookup value is after today, return 0

Reply | Quote