Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Last five entries

This is a discussion on Last five entries within the Excel Questions forums, part of the Question Forums category; hi, hopefully someone can help me out here, im a little stuck for a formula? i have a range K28:K97 ...

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,423

    Default Last five entries

    hi,

    hopefully someone can help me out here, im a little stuck for a formula?

    i have a range K28:K97 with numbers in each cell, now starts at row 28, but every week or so a new number is added upto a maximum of 97 (sometimes not that many)

    what i would like is a formula to show only the last 5 entries

    hope someone can help me out here

    thanks in advance

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,276

    Default Re: Last five entries

    To return the last five numerical values from K28:K97 in a range of cells, let's say B2:B6, try...

    B2, copied down:

    =INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-ROWS($B$2:B2)+1)

    Or, in reverse order...

    B2, copied down:

    =INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-5+ROWS($B$2:B2))
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,423

    Default Re: Last five entries

    great, thanks

    was thinking along the lines of

    "23, 34, 72, 22, 31"

    all in one line?

    is this achievable?

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,276

    Default Re: Last five entries

    Do you mean in separate cells? If so, try...

    B2, copied across:

    =INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-COLUMNS($B2:B2)+1)

    or

    =INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-5+COLUMNS($B2:B2))
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,423

    Default Re: Last five entries

    no sorry, in the same cell

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,244

    Default Re: Last five entries

    A1:

    =MIN(5,COUNT($K$28:$K$97))

    A2:

    =MATCH(9.99999999999999E+307,$K$28:$K$97)

    A3: List

    A4, copied down:

    =IF(ROWS($B$7:B7)<=$A$1,INDEX($K$28:$K$97,$A$2-$A$1+ROWS($B$7:B7)),"")
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,276

    Default Re: Last five entries

    Try...

    =INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97))&", "&INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-1)&", "&INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-2)&", "&INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-3)&", "&INDEX($K$28:$K$97,MATCH(9.99999999999999E+307,$K$28:$K$97)-4)

    Or, you can reverse the order, if you prefer. Also, if you define (Formulas > Defined Names > Define Names) BigNum as 9.99999999999999E+307, you can replace that big number in the formula with BigNum.
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  8. #8
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,423

    Default Re: Last five entries

    Thanks Aladin for the reply, but i will use Domenic's solution, much easier in one cell

    Perfect! thanks

    One problem tho, if there is only one entry, it throws up an error

    would it be possible to show only the one entry until more is entered
    Last edited by ExcelRoy; Jul 5th, 2010 at 03:14 PM.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,276

    Default Re: Last five entries

    Quote Originally Posted by ExcelRoy View Post
    One problem tho, if there is only one entry, it throws up an error

    would it be possible to show only the one entry until more is entered
    In that case, I would strongly suggest that you use Aladin's solution. Also, it makes it easier to work with if, for example, you want to sum, average, etc.
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  10. #10
    Board Regular
    Join Date
    Oct 2006
    Posts
    1,423

    Default Re: Last five entries

    Hi,

    Aladins solution only shows in different cells, not all in one cell, which is what im after

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com