Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Essex, England
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find last value (Excel 2002)

    On an Excel worksheet, Column A contains the months of the year entered as dates. Column B has charitable contributions provided by one donor, though there blanks for some months.

    In another cell I need to find the month in which the donor made his latest contribution.

    The following formula is the best I could come up with but it doesn't always work.


    =INDEX(Donations!$A$1:$A$12,ROW(INDEX(Donations!B: B,COUNTA(Donations!B:B,1))),1)

    What would be a more reliable formula?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find last value (Excel 2002)

    Try this:
    <pre>=INDEX(A:A,MATCH(9.99999999999999E+307,B:[img]/forums/images/smilies/cool.gif[/img])</pre>


    Steve

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find last value (Excel 2002)

    Or you could use :

    =INDIRECT(ADDRESS(MAX((B1:B12<>"")*ROW(B1:B12)),1) )

    entered as an array formula (CTRL+SHIFT+ENTER)
    Thanks,

    pmatz

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Location
    Essex, England
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find last value (Excel 2002)

    Many thanks indeed

Posting Permissions

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