Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Return Last 5 entries from column

Hi there

I'm looking to copy the last five entries in a column to another column ie

Spreadsheet1 A1 = 5th last entry in Spreadsheet2 J8:J18
Spreadsheet1 A2 = 4th last entry in Spreadsheet2 J8:J18
Spreadsheet1 A3 = 3rd last entry in Spreadsheet2 J8:J18
Spreadsheet1 A4 = 2nd last entry in Spreadsheet2 J8:J18
Spreadsheet1 A5 = last entry in Spreadsheet2 J8:J18

I've been able to find the last entry using the following
=INDEX('Data Sheet'!J8:J18,MATCH(9.99999999999999E+307,'Data Sheet'!J8:J18))
but I'm out of ideas on how to return 2-5th last entries.

Trouble is sometimes the first five cells in the table might not be populated so if its only the fist three cells populated then I need some sort of default for the 4-5th last entries.

Your thoughts appreciated.

Fred


Post your answer or comment

comments powered by Disqus
Hi,

I'm trying to get a formula that will count the number of entries from column A that appear in columns B,C or D one or more times.

E.g. 3 columns
A B C
52 50 48
50 51 52
53 49 50

So I would want the formula cell to return the value "2" because 2 of the 3 entries in column A appear in later columns. The entry "50" should only be counted once, not twice even though it appears more than once.

Hope that is clear. Is this possible to do in just one cell? thanks

dave

Is there an excel function to cut x number of characters from the end of a line of text? I want to remove the last 5 characters from each cell (all in column A). These are not blank spaces. The characters (zip codes) are different on every line (there are about 500).

thank you!

I have a dataset that grows each day. I'd like to be able to chart the last xx entries in the data set.

I'm familiar with Define Names and then using such names as the Data Source in Charts. However, I don't know how to write the OFFSET function to grab the last xx entries in a data array.

Let's say I had a data array that looked like the following:

3,636
3,859
4,503
3,556
3,879
3,899
4,069

And then the next day it looked like -- the new addition being 4,796:

3,636
3,859
4,503
3,556
3,879
3,899
4,069
4,796

Now lets assume that I always wanted to pull the last five entries from this dynamic range -- so on day one, my data points would be: 4,503, 3,556, 3,879, 3,899, and 4,069 and on day two it would be: 3,556, 3,879, 3,899, 4,069 and 4,796.

How would I do this? Thanks for making me smarter!

I have 20 numbers in a row (G34:Z34) I would like to average the last 5
entries. So if I have 10 entries (G34:P34),I would only like to average
(L34:P34) Which is the last 5 entered and continue on averaging the last 5
all the way to (Z34).
--
Geo

I am trying to extract the last 5 characters from a cell and enter them in a
seperate cell. I have several thousand addresses that I have in excel and I
want to pull the zip codes out and have them in a seperate cell so I can sort
them by zip code.
Basically cell A is a complete address and I want cell B to contain the zip
codes

I have a problem which is probably not that hard to solve, but I just can't seem to figure it out:

I have a column in which I continuously ad values (once a day). What I need to calculate is the average of the last five entries in the column so that it does this automatically every time I add a value.

Any help would be appreciated very much!!!

In Excel 2003, I have a list in a column. I'd like a function/formula that
returns the last value in the column.

For example, if the list looks like:
10
20
5
50
22

I'd like it to return 22.

Any suggestions?

Thanks.

How do I check if the last item in the column is unique from values above?
The formula should be dynamic as more entries are added to the bottom of the column the last item should always be checked to all previous entries.

ex.
a
b
c
c

the last "c" should be checked against values a b c

a
b
c
c
d

and as more data is added "d" should be checked against the others above

Hello,

So I've got col. A and B. I want to search the text in A, but without the last 5 char. in Col. B. Here's that code which works fine.

But now instead of displaying an X, I want to pull the last 5 characters from column B for which the code above would have
normally put an X. 

So, Conceptually I have this which doesn't work.



I am using a simple formula to keep track of some stocks, i.e. D2=Sum(B2*C2).
This formula is repeated in rows 2 thru 31. In cell D34 I want to display
the last previous entry in column D.

Thank you for your assistance.

I have a column of numbers follow by two blank rows then a calculation
using the last entry in the column.

How do I reference the last data entry?

When I insert a new row and copy the formulas the formula below the
blank rows doe not adjust.

Thanks in advance.

CW

I would like to set up a Excel spreadsheet for golf handicaps where it will
the 4 lowest scores of the last 5 entries. It must be able to work even if
there are missed entries (someone didn't show up/blanks ignored). I would
like it to work if there are only three scores used at the beginning of the
season, but when there are 5 or more scores, use only the 4 lowest scores out
of the last 5. The scores would start in column d. The following is an
example of scores with an underscore being a blank:
45,50,42,43,46,37,45,45,46,44
41,43,42,_,41,_,_,47,42,39
44,43,45,54,_,45,43,40,42,_
Our season is 18 weeks long, but I don't think that should matter with this
formula. Can you help me with this problem? Thank you.

I would like to set up a Excel spreadsheet for golf handicaps where it will use
the 4 lowest scores of the last 5 entries. It must be able to work even if
there are missed entries (someone didn't show up/blanks ignored). I would
like it to work if there are only three scores used at the beginning of the
season, but when there are 5 or more scores, use only the 4 lowest scores out of the last 5. I want it to average these scores. The scores would start in column d. The following is an example of scores with an underscore being a blank:
Golfer 1 45,50,42,43,46,37,45,45,46,44
Golfer 2 41,43,42,_,41,_,_,47,42,39
Golfer 3 44,43,45,54,_,45,43,40,42,_
Our season is 18 weeks long, but I don't think that should matter with this
formula. Can you help me with this problem? Thank you.

Could you please provide the formula to find minimum value in Column C and return its respective value from Column B.
In the attached example the output is red as the minimum value in Column C is 1.

I will appreciate the help.

Thanks.

Hi,
I want to count how many "W" are in the last 5 data entered. How can I do this? See attached sheet as an example - need to count the "w" in column C.

I want to automatically show the last 10 entries from "MASTER" sheet on "SUMMARY" sheet.

The formula I'm using is:

=OFFSET('MASTER'!W3,MAX((NOT(ISBLANK(('MASTER'!W3:W3000)))*ROW(('MASTER'!W3:W3000))-10,10,1)

I chose 10 rows x 1 column on "SUMMARY" sheet I entered CTRL+SHIFT+ENTER and I was told the formula contains a mistake in the formula????

Hi,
I am using the following formula
=AVERAGEIF(D$2:D447, D448, K$2:K447)
which gives me the average of all the values from k2 to k447 that meet certain criteria. Is there a way to get just the average of the last 5 entries that meet that same criteria rather than all entries.

Jared

Hi,

I am trying to return last two value of column B and G in different sheet. How to do so?

I got these data from Bloomberg. So normally, there is new value add after previous last row. In the ws tab, I just want last two value of sheet1.

Thanks

Hi
I'm trying to add the last 5 weeks goal difference of the team named in cell d2 on the odds sheet with the goal difference information for the same team on gldiff sheet.

Not every team plays each week so if a team does not play then DNP will appear in the cell. Some teams might play 5 times in a 6 week period or more so it needs to be able to tell that.
It does not make a difference if the team is playing home or away.

I've manually put two answers in columns:

Cell D2 on odds sheet: Accrington = 7 in cell F2 this figure in obtained by adding together the figures on sheet gldiff cell H2:B2 which is a seven week period but only played 5 times.
Cell E2 on odds sheet: Birmingham = 0 in cell G2 this figure in obtained by adding together the figures on sheet gldiff cell H8:C8 which is a six week period but only played 5 times.

The sheet gldiff will have new figures added in each week. The new week will always be the last data added to the sheet.

I don't know if it will be of any help but the game week will always be in column A on the odds sheet and row 1 on the gldiff sheet if you need to use it as a starting point for adding up the previous weeks.

I hope someone can help.

I have another question for you. Do you know of a way that I could have one cell equal the average of the last 5 values in Column C, so that when another value is added to the column it will still be the last 5 values? I need to have one cell equal the average of the last 5 values and one that will equal the average of the last 20 values.

Thanks!!!

Hello All,

I'm sure this will have been covered before, but I can't seem to come up with the correct search terms.

I have table with 5000+ entries, one column being a list of names. These names repeat a number of times.
Somehow I want to extract unique names elsewhere for use in a formula. I'm strugling to put this into words properly. Basically I would like a list of names, just as they show in an autofilter, but each name in it's own cell.

A link to a post where this has already been covered would be most useful.

many thanks.

Alex

Hello,

I was wondering if anybody could help with the workbook I am working on.
I have previously had some fantastic help with it but am a little stumped.
It works by agents entering their work totals for each day, on sheet 1. This is then transferred to the individual agents records.
From these agents records I then wish to generate averages.
I think this would be easy enough to do, but to make it easier on the eye, I have made the averages contained in columns F:I into data validation lists. But the problem is that my boxes only show one type of average, instead of the three.
So I think a macro which recognises what type of average is in cell F2:i2, and then changes the formula accordingly depending on the type of average.
For pnc Daily average, the formula would consist of sum of column B / by the number of entries, for conflicts would be column C etc
The pnc weekly average would consist of sum of last 5 entries in column B / by 5, for conflicts would be column C etc
The pnc hourly average would consist of the entry in column B / by the number of hours worked in column O.

Is this possible to do with the data validation list. I hope this makes sense, if anybody has any further questions if this is a bit unclear.

Kind regards

Joe

Hello All,

I need help with a command to selct and copy only the last 25 entries in column every time the macro is ran.

Any help would be appreciated.

Thanks

I have 20 cells in a row (G34:Z34) with data in the first 7 cells (G:34 thru
M:34)
I would like to average the last 5 entries and continue averaging the last
five as I fill the entire row with data. I would like to expand this row to a
greater lenght in the future.
--
Geo


No luck finding an answer? You could always try Google.