Identify Multiple Maximum Peaks In Data Set
Oct 8, 2008
I have a set of data about with approx 7500 cells all contained in one column. The data has a series of peaks that happen, and I need to identify each peak value and place it in a cell. The peak does not occur at regular intervals but they are somewhat regular, as in within 130-230 data points. So, if one could find the first peak in the first 200 cells, identify it and store it somewhere, then look in the next 200, store it, etc. I don't know how to do that in excel.
View 4 Replies
ADVERTISEMENT
Dec 2, 2013
I have raw data from a cyclic test that needs to be refined to generate a simple trend.
The raw data has about 1000 points to each complete cycle and im only interested in the maximum and minium values of the peaks. This would be easy but I am typically dealing with 15 cycles a second and in some instances up to 5,000,000 cycles in one test.
The amount of data is too large to be completed in one file but if done in parts I can then use a formula to reduce the number of max and min peaks chosen i.e. 1 in 100.
View 8 Replies
View Related
Dec 4, 2009
I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:
INDV DATE TIME FIX ....other attributes
INDV = unique code identifying the individual
The data is sorted by INDV, then by DATE, then by TIME.
I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
View 3 Replies
View Related
May 5, 2009
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
View 11 Replies
View Related
Dec 23, 2013
Book 1 sheet1 A
1234567
1234567
2658798
4083029
5507260
6931491
8355722
9779953
11204184
12628415
14052646
15476877
16901108
Book 2 sheet1 A
2658798
4083029
5507260
6931491
8355722
26587965
26365785
26143605
25921425
25699245
25477065
25254885
25032705
24810525
I need get the result Book 3 Sheet 1 A
All duplicates items from book 1 and book 2
View 6 Replies
View Related
Feb 23, 2010
to calculate the difference between peaks in an excel graph, but i need to do it between every two peaks.. I was just wondering could anyone tell me how to do this with some sort of formula or a vba file
Ive attached a picture. I need to calculate the difference between the blue peaks along the x-axis.
View 9 Replies
View Related
Jun 30, 2014
I was wondering if excel can auto label peaks and bottoms of a graph? Let me go further:
I have a graph that has one data number for every day of the year (365). This is a natural up and down curve, similar to a stock.
Can excel find the key high and low points? I have attached an example. In it you will see I have quickly labeled the key tops and bottoms. CHART TEST.xlsxCHART TEST.xlsx.
I basically omitted the peaks/bottoms that are very short term and irrelevant.
View 2 Replies
View Related
Dec 17, 2009
I want to find the maximum in column C if Column A = b but I don't want the number if it equals 11 or 9. So, the answer should be 7, but I don't know how to create the correct formula..I'm sure I have to nest a couple of things into an array, but I just can't seem to get it to work.
ABC
a17
a34
b27
b711
b109
c93
c86
d78
e44
e92
e31
f27
View 6 Replies
View Related
Nov 17, 2007
I have problem How to find Max of a column but instead see the conttent of the previous besde column on two criteria. and some one "UMH" from another forum helped and guided me much but unfortunately still my problem remains. http://www.access-programmers.co.uk/...d.php?t=138130. I attach the sample
View 4 Replies
View Related
Mar 16, 2014
Distance Calculations Example 1.xlsx
I need quickly identifying multiple locations based on their proximity to an initial location and each subsequent location selected. I have attached an example spreadsheet that is structured in the following manner:
Cell H2 is the 'initial' location that will be used to identify all subsequent locations
Colums A through C are location specific details including latitude and longitude of all 450 possible locations
Column D is a distance calculation based on the difference betwen the location in column A to the 'initial' location in cell H2
Cell F2 is the necessary minimum distance between locations(in miles)
Cell G2 is the number of locations needed(this will vary)
What I am trying to accomplish is to fill column H with the number of locations specified in cell G2 that are atleast the minimum distance in F2 apart from each other, like drawing virtual circles around the locations on a map.
I am pretty sure this can be done with a Macro, but am not sure if that is the best solution or not. I am a novice Excel user, so even though I don't know the solution, I can already see two issues:
1. Whatever the solution is, the reference in column D will need to change to calculate the distance for each subsequent location instead of staying absolute with cell H2(H3 for second location, H4 for 3rd, etc...)
2. With each selection that is made, any location less than the specified distance in cell G2 should be removed from any future choices for subsequent locations.
View 4 Replies
View Related
Aug 27, 2012
I have a spread sheet in which an account number is located in, lets say, column A. There can be multiple duplicate account numbers here, and thats ok.
However, there is another column, Col. B, which has the name of an action. There are multiple "actions" listed, but the each action can only occur once for each account number. I need to identify if an account number is duplicated and if so, does it have any duplicate values in B. If it does, highlight the duplicates.
View 1 Replies
View Related
Jan 3, 2013
I have managed to write some vba using sumproduct to identify multiple instances of a day in a persons schedule ... following is the code:
Sheets("Schedules").Activate
Range("A" & jj).Select
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Range("AF3").Formula = Evaluate("=SUMPRODUCT((A3:A" & LastRow & "= """ & WeekDay & """)*(B3:B" & LastRow & "="""
& ClName & """))")
[Code] .....
The sumproduct essentially checks to see for example that in the A3:AA range whether we have a match .... say a Monday that appears more than once for the client ... say John G. In the example I am using we have John G with three time slots on Monday that he is scheduled thus kkk = 3.
I would like to loop through the ranges in the Schedules sheet and copy those rows where the above statement about John G is true. For the rest of the example we just assume that John only has one scheduled time for each of the rest of the days of the week so in those instances kkk = 1.
I have the following code to loop thru the days of the week:
For j = 3 to 11
WeekDay = Range("AE" & j)
...
if kkk > 1 then
WeekDay = Range("AE" & (j-cntr))
else
WeekDay = Range("AE" & j)
Next j
There is a whole bunch of code, including he sumproduct above that then loops thru the Schedules sheet in the appropriate rows and columns copying the instances where John G has a schedule for a particular day of the week.
First I create a schedule sheet for John G and the vba code enters the first row of data ... Monday etc etc in Row A3 thru H3 of this new sheet.
On the second loop through the data on the Schedules sheet it finds another Monday for John G and this is where I would like to hold the WeekDay variable such that it is still pointing to Monday for a total of three loops since kkk = 3 for John G and Monday.
The problem that I am having is that if I hold WeekDay to point at Monday by something like WeekDay = Range("AE" & (j -1)), it seems to work for two instances ... that is the first instance, WeekDay = Range("AE" & j), j here is 3 for example, then the next pass we have WeekDay = ("AE" & j) and here j = 4 but since I reset it back one we are still at 3 ... this unfortunately only works once then I get and empty WeekDay = "". The loop has to loop through the Schedules sheet and I use jj as a variable here which increases by one each pass through ... next j, however ... increases j but then I try to reset it back by one so that it is still pointing to Monday in the Range("AE" & j) section of the Schedules sheet, it is a temporary range.
View 1 Replies
View Related
May 18, 2007
The following code takes the selected item from a listbox and populates another listbox with the item. The problem is that only the item from column 1 is populated to the other listbox. How can I populate the other listbox with the items from both column 1 and column 2?
Private Sub cmdSelToExc_Click()
Dim i As Long
With Me.lstProducts
' Copy all selected items, then delete.
For i = 0 To .ListCount - 1
If .Selected(i) Then
Me.lstExclude.AddItem .List(i)
End If
Next j
Next i
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then
.RemoveItem i
End If
Next i
End With
End Sub
View 2 Replies
View Related
Jul 28, 2009
Maximum value based on multiple variables. Basically what I have is something like this:
View 3 Replies
View Related
Aug 16, 2009
I have written a snooker scorebaord spreadsheet which keeps a history of highest scores and highest breaks, but am unable to find a way of showing these key values in a table.
Attached is a cut down version to demonstrate the problem. If you can provide a solution it can be either in formula or code.
View 3 Replies
View Related
Mar 1, 2013
I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).
Eg :
sno dept name marks r1 cs sush 55 r2 ece ram 68 r3 cs harish 77 r
So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.
View 6 Replies
View Related
Dec 22, 2008
I have a spreadsheet with 20+ columns. Each column contains a list of invoice numbers, eg, 1234. The lists are all of different lengths (see below). I want to find a way to find/highlight/identify those invoice numbers that appear more than once in the entire sheet (eg if invoice 1234 is listed in column B, and also in column H, and/or if invoice 5678 is listed in column A, and also in column J, and also perhaps in column D, etc.)
I've searched this forum but haven't found anything about searching for multiple duplicates in multiple columns. Ideally the matches would be highlighted in different colours, (eg all cells that contain 1234 would be red, those that contain 5678 would be blue, etc., for ease of identification) but I'm not sure if that's possible. I've attached an example that I've shaded myself to show what I mean. I've tried the MODE function but that only tells me which invoice number occurs the most.
View 5 Replies
View Related
Oct 23, 2008
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
View 4 Replies
View Related
Dec 11, 2008
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
View 6 Replies
View Related
Oct 9, 2008
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies
View Related
May 15, 2009
I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
View 2 Replies
View Related
Jul 23, 2012
Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.
I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).
View 5 Replies
View Related
Dec 29, 2006
I have four columns of numbers that range between 1 through 44 and span 100 rows. Each row shows a unique combination as shown below:
Row 1 - 07 18 11 09
Row 2 - 03 29 08 05
Row 3 - 01 20 11 44
Row 4 - 09 17 14 24
Row 5 - 18 11 20 26
etc...
Can anyone give me some tips on creating a VBA script that will scan a hundred rows of the data shown above and identify for me the rows where number pairs and number triples repeat? For example the data above shows that the pairs "18 , 11" repeat on Row 5 and Row 1. Can EXCEL perform such a task with the right VBA script or forumula? Is a VBA script necessary or will using an ARRAY be the correct way?
View 5 Replies
View Related
Feb 20, 2007
I have been easily identifying the bottom quarter of data using =IF(G2="","",IF(RANK(G2,G$2:G$100,1)<=INT( COUNTA(G$2:G$100)/4),"Yes","")) Now I need the top quarter and am not sure how to change the formula.
I have tried changing <= to >= but I then get the top 75% rather than 25%.
I have tried adding +50% and +.5 ... =IF(G2="","",IF(RANK(G2,G$2:G$100,1)<=INT((COUNTA(G$2:G$100)/4+50%)),"Yes",""))
View 3 Replies
View Related
Apr 10, 2007
I now need to look through 4524 rows (columns A,B,C) and identify any duplicates based on column A. After all duplicate data has been identified, I would like to delete out all non duplicates. I searched previous posts, but couldn't find anything that worked for me.
View 5 Replies
View Related
Jan 29, 2008
I'm looking for a formula or function to identify cells that are alpha numeric. The alpha is not necessarily in the same place (i.e. 1st, 4th. etc) in each cell. Some cells are all numeric.
What can I do to identify cells that are alpha numeric?
View 5 Replies
View Related
Aug 2, 2006
I have some code and it's not quite working.
For each row of data in my table (each row of data is contained in columns D:F) I want to display the maximum value in column H, of the same row. For some reason though it is putting it in 21 additional rows down.
Sub Winner()
Dim lngMax As Long
Dim lngRowLast As Long
Dim rng As Range
Dim rngAll As Range
Dim rngRow As Range
lngRowLast = Range("D65536").End(xlUp).Row
Set rngAll = Range("D2" & lngRowLast)
For Each rng In rngAll
Set rngRow = Range(rng, rng.Offset(0, 2))
lngMax = Application.WorksheetFunction.Max(rngRow)
rng.Offset(0, 4).Value = lngMax
Next rng
End Sub
View 4 Replies
View Related
Feb 12, 2014
create a formula for my data. I want to pun binning as per criteria given.
Bin 1: IR < 150 & VR >62
Bin 2: 150<IR<300 & VR >62
Bin 3: IR >300 & 62< VR <30
Bin 4: IR >300 & VR <30
Example data
Data 1 : IR = 80 & VR =68 --> Bin 1
Data 2 : IR = 200 & VR =68 --> Bin 2
Data 3 : IR = 300 & VR =25 --> Bin 4
Data 4 : IR = 350 & VR =45 --> Bin 3
View 10 Replies
View Related
Jun 11, 2009
I am using this sheet as an example
[url]
the data contains date/time info in column A and weight data in column B which cycles for when a tank is filled and subsequently emptied (indicated by values below 100)
I am looking for a script that will:-
a. identify these cycles using the column b data and seperate out or return the date/time values for cycle start and end
b. report the max value in column b for each cycle
The max value of b data will allow me to know which product was in the tank. I will then know the date/times for each time a particular product was made. I then want to extract the data from the rest of the columns for the specified dates
I am hoping this sounds lot more complicated than it actually is but I am completely lost when it comes to programming VBS and macros so I desperately need some help.
View 14 Replies
View Related
Dec 21, 2006
I have a column of data that comes from simple division formulas, E2/F2. I am wanting to take the Percentages given and quickly recognize the ones in the bottom quarter.
100%
75%
23%
39%
50%
N/A
100%
5%
8 items - the lowest 2 (the bottom quarter) should "stand out" - 5% and 23%
I figured two options:
1.) Conditional Formatting - don't know what the formula would be to use
2.) If statement - preferred method.
I would like in the cell next to the percentages for there to be a blank cell if the amount is not in the bottom 25% and if it is I would like the cell to read "Yes"
I thought something along the lines of =if(G2 in range(G2:G100)is bottom(.25),"Yes","") Of course that is not real code. I'm hoping someone will be able to edit that to make it functional. Or guide me in the right direction.
And you may have noticed the "N/A" in the list above. I would like excel to view this as 100%, at the least just ignore it and don't give me an error.
View 4 Replies
View Related
