Last five entries

Thanks:  0
Likes:  0

1. ## 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

2. ## 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))

3. ## 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. ## 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))

5. ## Re: Last five entries

no sorry, in the same cell

6. ## 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)),"")

7. ## 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.

8. ## 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

9. ## Re: Last five entries

Originally Posted by ExcelRoy
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.

10. ## Re: Last five entries

Hi,

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

## User Tag List

#### Posting Permissions

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