I attached an example.
I am trying to figure out how I can write formulas that look for trends in the data. I will be entering more data every day. I need to look at the 5, 10, 20 most recent results for each number.
For example, I need to see how many times yes was answered for #3 in the most recent 5,10, 20 entries.
I am sure there is some simple way to do this with a control + f formula, but I don't know how to do it.
I need help with summing the corresponding data to the most recent 7 days.
I have the dates (dd/mm/yyyy) in row C1:AH1 and data immediately below it in C2:AH2. I believe that using some formula including SUMIF & LARGE might work but I haven't been able to figure it out myself or find examples posted in the forums here.
If I have 50 data points sorted in order (oldest --> newest), how do I do a stdev function that will only pull the 24 most recent points?
Essentially, I have 14 columns with 50 data points each. At the end of the list (or maybe beginning - under the column header) I want to do a stdev calculation referring to the most recent 24 points and a second for the most recent 36 points.
I've been trying to create a pv table that will count all the trades based on products, but they are duplicate in my set of data that I am trying to eliminate, I just want to select the trades with most recent date and if there are duplicates entries with the same date, then to select the aging column..
Column A are my trade IDs (duplicates trades), column G my dates and column O my aging days (goes up to >5). What I'd like to do is to count the trade using the most recent date and if the date is the same use the aging column. is this even possible to do using formulas?
I am building a shared workbook that will have the same data on multiple worksheets.
The problem is that users are going to be entering updates to that data on individual sheets, not going thru and updating each sheet.
Also, some of the updates will be themselves updated as the day goes on.
What I need is for the workbook to only use the most recent data/update on all of the sheets.
Example: Sheet1 will have all of the info sorted by time. Sheet2 would have only the data for account "A" sorted by job number. Sheet3 would have account "B"...
When a change is made to any of these sheets I want the other sheets to be updated.
Now that I have wrote this out it seems to me that a "worksheet event" macro might do this for me...
But can you write an event macro into all the sheets that would not result in a loop (event macro on sheet1 changes sheet2 which triggers the sheet2 event macro...)?
how i can select the most recent 10 cells from a list of data to be included in a formula. I have selected it manually, for example F122:F131. This is fine but i don't know how i can put it so that it will update to the 10 most recent results when i input another line of data. So when i put in more data it stays on the previously selected. I want it to move shift down to include the new data but only the 10 most recent.
View 5 Replies View RelatedI have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View Relatedvba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
I have 31 cells (say in A1 thru A31), representing days of the
month, that daily number values will be entered. I want to be
able to select the value in the most recent day cell, not the
most recent updated cell...
i.e. the cell in the A1:A31 range closest to the A31 cell.
For example...
Say I have values: 2,4,7,12 in A1 thru A4, I want the value
in A4, even if I just updated Day 2's cell... because A4,
or Day 4, is closer to the end of the month.
I have tried nested ifs and vlookup to compare two sets of data and change the first set of data if it is than the first. But leave it alone if it either is the same or does not exist in the new set of data. It sometimes seems to work but i find it is not consistant. It looks simple but i think i am missing something.
if column A has identifiers and column B has results then it should work if the identifiers in column C are found in column A and it looks to see if column B and D are the same, then change B if different but leave it if either it is the same or not there.
A
code1
B
36
C
code1
D 33
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies View RelatedI want to import data with a web-querie. The data consists of numbers in a column (14 cells).
I want to poll the info in the table in the webquerie every 15 minutes to see if the info in the table is changed. Therefore I refresh the info every 15 minutes, and when the info is changed, than the data must be put in a new sheet in a column and the columntitle must be the date and time that the info is put into the column. Can this be done in vba? I suppose that to compare the info of the new poll with the info of the previous polling the previous info must be put in a "compare" table.
Column Q is a vlookup which looks up dates contained in column P within a range called "Query_From_Price_Database" and returns data from column 3 as indicated below:
=VLOOKUP($P4,Query_from_Price_Database,3)
What I'd like to achieve for each time a value is returned as being "0", is for the cell to display the most recent data.
E.g.The vlookup results might be:
27/04/10 15.29
26/04/10 15.55
25/04/10 14.98
24/04/10 0
23/04/10 15.13
22/04/10 0
21/04/10 0
20/04/10 0
19/04/10 15.24
I want it to be displayed thus:
27/04/10 15.29
26/04/10 15.55
25/04/10 14.98
24/04/10 15.13
23/04/10 15.13
22/04/10 15.24
21/04/10 15.24
20/04/10 15.24
19/04/10 15.24
Where there would be zeros, data is displayed from the most recent date actually containing non-zero data. Is this possible with a "not too complicated" formula?
I've tried a few methods, but they only seem to result in "formula too long" errors.
I am familiar with SUMPRODUCT and {SUMIF}, but I am stumped on how to create a formula that can sum (or count) the only the most recent entry for each Name. The Name field is dynamic and constantly updated with a unique reference number, so I don't want totals by Name/Type, but rather only the newest Ref for each Name by Type.
Ref__Name____Type_QTY
8____John____B____5
7____Bob____B____4
6____Dale____A____3
5____Jane____C____2
4____Bob____B____5
3____Jane____C____4
2____John____B____3
1____Bob____A____2
Should return;
Recent_Type____NOT Total
5______A______5
9______B______17
2______C______6
Hopefully this explains it all. Looking forward to any input or suggestions to keep this a simple automated process.
*added _ to space out the columns
I have one spreadsheet with a list of materials by material number. I have another with all sales activity. How can I pull into the first spreadsheet the date of the latest activity from the second spreadsheet?
View 2 Replies View RelatedI have a workbook to keep track of names and dates of a specific action. There are 10-15 names that recur on the list. The most recent action is added to the bottom of the list. The names are in column I and the date of the action is in column J.
The problem is having to scroll through the list to find the most recent action for each person. Ideally i'd like to have a list of the names in column N and then in column O the most recent date in the list for each name.
Is there a way to list the recent changes to an excel document on the document itself?
For example, I have just 4 or 5 columns in the sheet, but out the the right on the 7th column I'll have a header that says "Recent Changes". Below that for X amount of rows, I would like it to keep a running change list something like this:
B14 was changed from '6.021' to '6.5' by username on 10/21/07 3:45 pm
A23 was changed from 'Sally' to 'Billy' by username on 10/21/07 2:30 pm
I thought this up by looking at the way the "Track Changes" functionality works on the Tools menu. That will highlight the cells in blue as they are changed and will also let someone look back through changes to let them accept or deny.
I have a table in a sheet where the rows of data are filled in automatically from other sheets. Each row represents a different week. The data, (lifted from from the relevant worksheet for each row) represents the no. of calls in each week.
Now, I've set up an average to be calculated from the data - it'll do this for every row as long as there is data present. (If the row's corresponding worksheet doesn't have any data the result will be "NA()" - not included in the calculation.)
However, I don't want the average to include the no. in the "most recent" row, as this is "live" data and until the week is over does not represent the data for a full week and therefore skews the average.
So I want the average to exclude the data in the most recent row with a numerical value in it and not a "NA()"
ie - column could be ......
I have data, which has an oil well that has had sections of its pipe perforated.
This has all happened on different days throughout the wells life.
I need an equation that says TRUE if the Perforation is the Most Recent for that well.
But I also need it to say True at different Top and Bottom Depth intervals throughout a specific well.
(for example: the last row in this data. Says TRUE even though there are more recent dates in this well.
This needs to say true because this is the most recent perforation in the top and bottom depth interval 9526 to 9536.
The other most recent perforations happened in shallower intervals, ie. 9456 to 9480.
So I need something in the equation that will say true to the most recent start date, if that top and bottom depth interval has not already been selected as true.
I need to have a column that says True IF the start date is the newest (most recent) for each individual well.
Easy enough.. But. I also need this to say True multiple times at different dates for each well.
For example below. it says true on 7/22/2001 three times and then True again on the last row on 10/14/1992.
It has to say true there because this is the most recent perforation that Top_Depth and Bottom_Depth interval.
WELLNAME
TOP_DEPTH
BOTTOM_DEPTH
START DATE
IF most recent Perf
RA-0001
9380
9395
10/14/1992
FALSE
[Code] ......
Looking at a series of rows that contain dates (in one column).
Return the value of the cell in the column next to the most recent date.
I can get the most recent date using MAX, but how do I tell Excel to return the value of the cell next to it?
I have data resembling the following
Col A
Sun
Sun
Sun
Sun
Col B
12/08/2007
13/08/2007
14/08/2007
15/08/2007
Col C
A
B
C
D
Is there a formula where I can extract the entire row for the most recent date, in this case Row 4 containing Sun,15/08/2007,D would be returned.
I want to lookup an ID which there may be three of, but I would want the most recent date out of the three ID. I can get this to work by putting the column in ascending order but this is not very practical.
Is there any way I can do a lookup and add a date parameter to lookup the most recent?
I would like to sum a range of data based on the most recent 12 months.
Details:
Row E9:?9 - Contains random dates
(where "?" will change based on the user will be min 12 and max undefined)
Column C - Where I want the sum formula - to sum that corresponding rows data based on the most recent dates listed in row 9.
I'm tying to finds the most recent X or O. Then takes the price on that day and compares it to the current price and based on the difference either higher or lower puts out an X if the current price is higher and an O if the current price is lower by the Half StartData - however when i get to about 6 IF statement it freeze up and it wont give me the X or O's ...
View 9 Replies View RelatedI have spreadsheet set up to calculate me and my friend's handicaps. I have set it up almost completely. The only problem is that I forgot one little detail when setting it up: handicaps only calculate the 20 most recent rounds of golf.
Rather than only storing the newest 20 rounds, I would rather add a layer to my formula to filter out older records for me.
=ROUNDDOWN(AVERAGE(OFFSET('Score Entry'!$I$2,,,VLOOKUP(COUNTA('Score Entry'!I2:$I$400),Rounds,2,TRUE)))*0.96,1)
In the "Score Entry" tab on my spreadsheet the date is in column D. Is there someway to modify this formula to filter anything beyond the previous 20 rounds?
For the sake of better communication the "Rounds" named range is a list of the number of total rounds played and the corresponding number of rounds that should be used to calculate the handicap.
I would need Excel to compare rows between two worksheets (A & B), and if there are any:
(1) new rows in A, to copy across the new rows onto B.
(2) updated rows in A, to copy the updated data onto B replacing existing B values.
This in itself would be simple. Only thing is, worksheet B would be subtotaled and sorted by the subtotal and this is something I wonder if Excel can cope with. BTW I wouldnt mind it if subtotals in B has to be undone before the data gets updated, so long if Macro automates it for me. Is this possible or am I asking for too much? A representative excel file is as attached.
I have a list of numbers column A and a list of dates in column b, i need a formula which looks up the most recent for each of the numbers. can this be done?
View 3 Replies View RelatedI coordinate nearly 200 volunteers for a small non profit using excel. I use excel to keep track of all volunteer information as well as for scheduling shifts. Currently I'm looking for a way to show what the most recent shift a volunteer performed was.
All of this information is kept in different tabs in the same document.
In the "Volunteer" tab you'd find all of the information for the volunteers: name, phone number, email address, number of shifts they've completed, etc.
In the "Shifts" tab you'd find the information for each shift including: Shift Date, Name of Volunteer, Location of shift, etc.
I'd like to find a way to show in the "Volunteer" tab what someones most recent shift was. The only way I've been able to think of would involve a ridiculously complicated series of =if() statements, which would be more hassle than it's worth.
I've had my Excel 2007 set up for the longest time to show the 30 most recent files when clicking on the office button. After changing it to 50 the office menu in Excel runs off the bottom of the screen and I can no longer get ot the options at the bottom to change it back. Does anyone know the registry key to modify this setting back to the 30 that allowed the full menu to be visible?
View 2 Replies View RelatedI have an employee summary sheet, and another sheet where data will be entered by date.
On the summary sheet I want to gather only the last 3 dates that were entered.
Attached is an example.