Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    automatically update value in formula to last entry

    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<>"")))
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    skipro (2011-03-23)

  4. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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?

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    What does the "MAX(0" do in
    =MAX(0,TODAY()-LOOKUP(9E+300,A:A))

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

  7. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It replaces the IF part of your formula - in other words if the lookup value is after today, return 0
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •