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.
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.
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.
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 View RelatedI 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.
I manage a team in a call centre and deal with lots of reports on excel.
I am dealing with a report on a daily basis that i need to use in order to calculate the total idle time that each of agents have. There are roughly 170 agents.
After I have formatted the cells to hh:mm:ss, the obvious thing to do would be : =(B1-A1)
Is there a way to write a macro (or anything) in order to reapply this to the report on a daily basis? Bearing in mind that this has to be done daily for 170 individual reports. They work a 8am - 5pm shift. So there is an entry every time they make a call.
Agent Name
Extension
Initiateddate
TerminatedDate
MSGR_nmasango
9363
28/02/2013 8:06:47 AM
28/02/2013 8:08:54 AM
[Code] ........
I have a pivot table and I created a formula that says:
=jobCompletedDate-jobCreatedDate
and so for example, I have
3/31/2014 7:21AM - 3/31/2014 6:33AM
and that difference is giving me: .03367
I'm not sure what this value represents......the difference in dates, converted to ??
whats the difference between vba excel 2003 and visual basic 6.0 I don't understand, if it is the same or complete different.
View 3 Replies View RelatedAs you can see in attached file (hopefuly you can see it) pivot table Summed sales in 2014 and 2013 in column GRAND TOTAL. Insted of summing this two years, I would like to show difference between them, but keep this layout!
PROBLEM.jpg
I tried to use prtScrn button on the keyboard to capture the screen (excel UI) and then pasted it to excel then saved it. I tried to do the same thing on my friends machine and what surprises me is that the file size of two excel is different.
We use the same OS, excel version and the same machine specs.My saved excel file is around 7.91 mb in size while his is just around 235kb. Why is this like this?
Both pasted image have bmp as a format (default for excel when a you use print screen and directly paste it on excel.).
Excel 2010 - Solver will NOT work with whole numbers and that is what I need (only whole numbers, i.e whole vials being used). So this is a drug/inventory problem. The drug is dosed as 25, 35,or 50 units/kg. Also a Max dose is reached with 100 kg. (this I can do). The trick is we want to use whole vial sizes and the vials come with varying amount of drug per vial (480-620 units/vial). We can enter our existing Inventory to get the exact units/vial and number of vials on hand. Now I need to figure out the best combination of vials in Inventory to use to get the closest to the dose needed. If Max dose is reached then the MOST we can go over the max dose is 5%. I'd like to see the percentage difference from the calculated or Max dose.
View 7 Replies View RelatedI am running a text comparison between customer names in 2 systems and although I can identify where they dont match I would like a bit more information as to what the differences may be:
See below sample data - 1 system may have Mr/Ms and middle initials whereas the other simply has the name is it possible to write a formula that will look at the cell and when it does not match identify the extra characters. Or if it totally doesnt match then just state incorrect.
A
System 1
System 2
Desired Result
Anne Duffy
Anne Duffy
OK
[Code] ...........
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
DAILY TRAIN RUNNING.xlsx
I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
I read from one of the posts here and see sum(a1/a2). I tried it on excel and see no difference between sum(a1/a2) and (a1/a2). if there is a difference, could you please highlight to me? If not, why put 'sum'?
View 2 Replies View RelatedI am trying to work out to get the following result.
Using Cell CB5 as a Date Of Birth, I want to be able to have cell CA5 return "Yes: if the following is either met..
If under 45, Cell
I have two timestamp fields from which I need to extract the difference.
[Code] ..........
The formula is B2-A2 and the Difference field is a custom field using h:m:s.
As you can see, the difference is correct, except in military time. The correct answer should be 5:41:33.
I'm currently doing some research for the World Cup (Soccer) and I want to create a formula that finds the largest gap between two dates. Basically, I'm copy and pasting player data into an Excel template I've created and one of the columns in each player's data is a list of dates when he has played over the last 12 months. I want to create a formula that shows me the length (in days) of his longest break from playing competitive football AFTER Oct 1st 2013.
View 5 Replies View RelatedI'm trying to automate the attached schedule so that the formulas in H stop increasing once the amount in column J equals zero. So far everything I've tried either gives me a circular reference error or ends up giving me the same result as if I depreciated the asset an additional month.
View 3 Replies View RelatedI'm trying to figure out a formula that tells me how many reports are overdue.
A report is due every six months. There may be times when more than one report got missed.
Right now, I have the Y6 recognizing that a report is late... period.
=IF(V6>(TODAY()),0,1)
So, what I need is:
If the Time Difference between V6 and T6 is greater than 6 months, divide the difference by 6 mos and return the answer to cell Y6 (rounded down with no decimals).
See attachment.
Could someone explain to me what the difference is between these the two examples given in this worksheet?
View 11 Replies View Relatedi m try to use the lookup function but not sure which one i want
the cell to look up is e1
the cells it could be in are a1:a20
the answer will be next to the answer in b1:20
I learnt a new formula from this forum which -> if(n=(a1),a1,"S"). I use another formula -> if(iserror=(a1),a1,"S"). It comes out the same result.
May i know what is the main difference between these two formulae?
what's the difference between cell a1 and b1?. see attachment.
View 3 Replies View RelatedI need a formula that gives me the difference between two different times
EG. 11:14:56 and 16:14:26, i want to find the difference/time between the two. Hope i'm making sense...
Also, does the time have to be in a time format on excel for the formula to work?
I have two 2 Dimensional String Arrays with data. I need to find a way to get the difference between these two Arrays. I am new to VBA, I don't know how to deal with these. I certainly feel that there is some efficient function for doing this. or Is the naive two for lop concept is the only way to go?
View 2 Replies View RelatedFor Eg: i have 1000 students...i entered marks to all the students now i
need to fine the total students who have score >50 and <60 in each subject..
I would really appreciate your help
I have a client who weants to work out the total number of hours (not minutes) between two times. I have managed to do that with no problem using the formula =IF(A2>B2,B2-A2+1,A2+B2). However, this is where the problem starts.
They want to multiply the number of hours with the number of men on the job, but the answer is wrong, and I cannot understand why. I have checked the formnat of the cell and changed it to see if that is the problem, but without success.
I have copied it below
Time inTime outNo hoursNo of MenTotal Man Hours
12:0003:001526
14:0018:00474
I have a spreadsheet that records a bunch of golfer's scores for a round of golf.
I have a range G10:X10 that shows Par for each of the 18 holes.
I have many rows below that, G11:X11 is one example, that are individual golfer's scores.
I'd like to add a column, say in column AC, that would count the number of birdies each golfer had in the round.
Thus, I was thinking something like this in AC11:
=SUMIF(G11:X11 - G10:X10,"=-1").
Of course that doesn't work. I need some way of creating a range of 18 differences for the first parameter of the SUMIF function. I know that I can write a VBA macro for it or add another row for each golfer with the difference (but that would double the size of the spreadsheet). Is there an elegant way to do this with a worksheet function given just the scores and par for all 18 holes.
I'm trying to calculate the number of hours an agent works between the hours of 7AM and 7PM. Column B has their START time, Column C has their END time, Column D includes their LUNCH time, and Column E calculates the total number of hours worked (=IFERROR(SUM(C248-B248)-D248,"-").
I've created 3 additional columns (Column F = number of hours before 7:00, Column G = number of hours after 19:00, and Column H = Total excluded hours which represents the total number of hours an agent worked before 7AM or after 7PM.
I've attemped several different formulas, but they all give me '#########' in one cell or another.
Other formulas used:
=$F$243-B248
=ABS(F243-B249)
=-IF(B250>$F$243,-1,1)*MINUTE(IF(B250>$F$243,B250-$F$243,$F$243-B250))
My format is 13:30:55
I'd like for the result to be either a dash "-" or "0:00:00" if an agent's start time is after 7AM or end time is before 7PM.