Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I have a column that contains a running total. What worksheet formula can I use to get the value from the lowest nonblank cell in the column?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say the data are in column A. The following formula will return the last numeric value in this column:

    =LOOKUP(9.99999999999999E+307,A:A)

    (9.99999999999999E+307 is the largest value that can be entered in a cell, so it is very unlikely that this value will be found, hence LOOKUP will return the last numeric value it encounters)

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Thanks, I had just struggled my way to
    =OFFSET(F1,MATCH("",F:F,0)-2,0)

    I have no idea whether that is more or less effective than your solution.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I get #N/A when I try your formula...

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='HansV' post='782419' date='30-Jun-2009 13:38']I get #N/A when I try your formula...[/quote]

    Strange, must be something special about my workbook. I just cut and pasted that into my workbook and it still gives the correct value.

    Anyhow I am quite safe using your formula. If my bank balance ever exceeds 9.99999999999999E+307 I expect you'll read about it in the newspapers.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When your bank balance exceeds 9.99999999999999E+307, you OWN all newspapers...

Posting Permissions

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