Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Windows XP, Excel 2007, sp2

    I have a list of numbers which relate to cumulative payments per period.
    Given we have 52 periods in the year,
    The first few periods of the year may contain zero, and the final periods of the year (as yet) contain zero.
    The values, although cumulative, may go down or up from one period to the next.
    I want to find the last entered number in the list.
    e.g. Typical example:
    Col A, rows 1 to 52 will contain the integers 1 to 52
    Col B rows 1 to 9 will contain 0
    Col B rows 10 to 12 contain 23.50, 57.00, 36.50
    Col B rows 13 to 52 will contain 0
    The number I want from this list is 36.50 as that was the last entered value

    I have no other indicator as to which period was the last period
    I cannot obviosuly use MAX as the numbers may go down
    Counting number of zeros is not helpful because zeros appear before and after our numbers.

    any help appreciated.
    Thanks
    alan

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you are truly only looking for 36.50, then you can take the range B1:B52 and create a conditional formatting rule that highlights any cell in that range that contains that value. Create another rule that sets the font to white if the values are zero. That eliminates the clutter.

    On the other hand, if you are really wanting to test for non-negative values, then instead of, or as well as rule one, create a rule thats test cell values for values greater than zero (or non zero).

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by ase001 View Post
    Windows XP, Excel 2007, sp2

    I have a list of numbers which relate to cumulative payments per period.
    Given we have 52 periods in the year,
    The first few periods of the year may contain zero, and the final periods of the year (as yet) contain zero.
    The values, although cumulative, may go down or up from one period to the next.
    I want to find the last entered number in the list.
    e.g. Typical example:
    Col A, rows 1 to 52 will contain the integers 1 to 52
    Col B rows 1 to 9 will contain 0
    Col B rows 10 to 12 contain 23.50, 57.00, 36.50
    Col B rows 13 to 52 will contain 0
    The number I want from this list is 36.50 as that was the last entered value

    I have no other indicator as to which period was the last period
    I cannot obviosuly use MAX as the numbers may go down
    Counting number of zeros is not helpful because zeros appear before and after our numbers.

    any help appreciated.
    Thanks
    alan
    How about having the following in C1
    =ROW()*(B1<>0)
    and dragging down
    =MAX(C:C) will then give you the last row number containing non-zero.

    The following will return the value (rather than the row number)
    =INDIRECT("B"&MAX(C:C))

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Guys.
    Mark, the conditional formatting works great, but I really need a value returning to be able to use that value elsewhere.
    GFamily (Runcorn - just round the corner from me..almost) - that solution prompted me to try an array formula. The solution you show works great, but I unfortunately don't have luxury of being able to add columns to the worksheet.

    so far, I've found this to work pretty well:
    given any column of numbers I can find the last entered number using the array formula:
    {=OFFSET(Q1,MAX(IF(ABS(Q4:Q55)>0,ROW(Q4:Q55),0))-1,0)}

    As I had negative numbers in one of the lsits I had to take an absoute view of them, then return the row number of each one in the array where greater than zero. From there I could see the maximum row number and then just use that in an offset.
    My list spanned from row 4 to row 55 but I still used row 1 as the offset reference and subtracted 1 from my MAX value to get the actual row which contains the last number.

    thanks for the input guys.
    Alan
    Warrington

  5. #5
    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
    You can do it without the intermediate calculations with the formula:

    =INDIRECT("B"&MAX(IF($B$1:$B$52<>0,ROW($B$1:$B$52) )))

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Steve, works well as an array formula.

Posting Permissions

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