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.
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 a worksheet containing data in columns. For each column, I would like to calculate the standard deviation of the mid-95% of the values i.e. 2.5th percentile to 97.5th percentile (to reduce the effect of "outliers").
So first of all I calculate the values of the 2.5th and 97.5th percentiles:
A102 (2.5th percentile) = PERCENTILE(A1:A100,0.025)
A103 (97.5th percentile) = PERCENTILE(A1:A100,0.975)
Then using an array formula shamelessly plagiarized from this very website, I calculate the standard deviation of the values that lie in that range:
{=STDEV(IF((A1:A100>=A102)*(A1:A100
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 put together some code which at the end of it I am wanting to show in cell D1 the creation date for the most recently created file found.
View 5 Replies View RelatedI need to create a macro that can produce a new workbook with the list of the most recent users of my company, so that we can print their membership cards.
From my company's admin I can export the complete list of users with their name, serial number and address. Every time we export this list, the file is saved as "userdata-dd-mm-yyyy.xls", and "dd-mm-yyyy" as today
However, I would like to create a macro that would
1) open the most recent "userdata-dd-mm-yyyy.xls" workbook in the folder,
2) compare the user numbers in this file with the most recent "card_order-dd-mm-yyyy.xls" and
3) produce a new "card_order-dd-mm-yyyy.xls" workbook with the list of all new users since the last "card_order-dd-mm-yyyy.xls" ("dd-mm-yyyy" as today), based on the highest number for user number (this is a continuous list)
Note, the "userdata-dd-mm-yyyy.xls" workbook has name, serial number, address, expiration date and user number as columns.... but the extracted info to "card_order-dd-mm-yyyy.xls" is just of the name, serial number and expiration date.
What is the easiest way of doing so and what code should I use for the macro?
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.
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...)?
I'm using the following formula to calculate the % relative standard deviation of 5 values:
ROUND((STDEV(C8:C12)/AVERAGE(C8:C12)*100),6)
where C8 through C12 are all 3.48.
I was surprised to see the result of 0.000002, and not 0.000000. This formula seems to be accurate to 6 decimal places when using 5 values that are not all the same. When they are the same, I get this small discrepancy in the 6th decimal place. I attempted to troubleshoot by inspecting C8 through C12. The values were typed in as seen above (3.48) and the cell formatting is set to 2 decimals in each of these cells, so the cells themselves shouldn't be contributing to the issue.
When I click on the "Office Button", then in the recent "documents dialog box" there is a list of my worksheets plus an "allsenatename" file". This then downloads a worksheet from a website. I don't know how it was added to my list, I must have entered it when looking at that web site in error.
View 3 Replies View RelatedI have a set of values. #N/A413636363632. The #n/a is there on purpose for charting reasons. How do I calculate the stdev of that range? i.e. ignoring the error?
View 2 Replies View RelatedI'd like to calculate the standard deviation and mean of a whole load of data. Unfortunately it has some errors, FALSE values, blank cells and zeroes I'd like to exclude. Calculating the average is no problem as the AVERAGEIF function works fine, but there is no equivalent for STDEV. The (array) formula
STDEV(IFERROR(E29:E32,FALSE))
ignores the cells with errors and calculates the SD, and the (array) formula
STDEV(IF(E29:E32>0,E29:E32,FALSE))
ignores the cells with a value of 0 in them and calculates SD.
How can I combine the two into one formula?
I have trouble with calculating standard error with multiple criteria. Below is the formula I used:
=STDEV(IF($J$2:$J$25="A",IF($K$2:$K$25="B",IF($L$2:$L$25=1,M2:M25))))/SQRT(COUNTIFS($J$2:$J$25,"A",$K$2:$K$25,"B",$L$2:$L$25,1,M2:M25,""))
The problem is the blank cells in the range that I am calculating (M2:M25). Everything is alright if there were no blank cells in M2:M25. If there were any blank cells, however, the STDEV was wrong (the countifs part was correct). It treated the blank cells as zero.
I need to calculate a Stdev and Average based on a specific range. However, my range is not constant in the time. For instance, today I may need to calculate these functions based on 30 numbers, but tomorrow on 25 or 15 or 50...
Is there a way to automate this process by changing the number of data points to include in the range in one single cell and avoid manually adjusting the range in accordance with needed data points?
In other words, I would like to change the value, let's say in Cell A1 from 30 to 50 and then Excel would re-calculate the StDev and Average based on 50 data points and not 30. Is that possible?
I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.
View 8 Replies View RelatedI want to get the MEDIAN of the values in F2:Q107 only if the value in D2:D107 = D111, how do I do this. I want to do the same thing with MEDIAN.
I don't want to use a pivot table if I can avoid it.
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.
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.
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 Related
