Number Of Entries Above Certain Value
Aug 11, 2014
To give this a bit of context, I am looking at running speed of an athlete. I am trying to determine the NUMBER of sprints in a session (aka >15 km/hr for example). To do this, I need to calculate the number of times a speed vs time graph surpasses 15km/h.
For example, using this data:
TimeVelocity
0.212
0.413
0.614
0.815
116
1.217
1.416
1.615
1.814
214
2.212
2.410
2.613
2.814
315
3.216
3.417
3.618
3.817
415
4.214
4.413
4.612
4.812
511
I would calculate two sprints (one beginning at time point 0.8, one beginning at time point 3). I can't work out how to calculate these sprints independently, rather than counting the total number of entries above 15km/hr (11 in this example).
View 3 Replies
ADVERTISEMENT
Jun 18, 2008
I have a list of ID's, many of which appear several times. Is there a formula that will give me the number of different ID's? That is:
CON001
CON100
CON050
CON001
the formula would give the answer "3" for 3 different numbers in the 4 total numbers.
View 9 Replies
View Related
Apr 16, 2014
I have attached a workbook which I am trying to calculate the number of "Booked" entries there are between 0 -36 months, 36 -72 months, 72 - 96 months and 96 months plus, for some reason the 0 - 36 months is calculating correctly but the remainder aren't, the current formula that I am using is;
=SUM(COUNTIFS('Service Reminders 2014'!$AR:$AR,"Booked",'Service Reminders 2014'!$K:$K,"<="&EDATE(TODAY(),-36),'Service Reminders 2014'!$K:$K,">"&EDATE(TODAY(),-60)))
Reminders.xlsx
View 8 Replies
View Related
Nov 12, 2008
I have a list of words in one column, some of which feature more than once, in random order, i.e.:
Bird
Plane
Superman
Superman
Plane
Superman
Bird
Plane
I want to have a function that counts the number of times each word appears, so in the cell next to each entry for "Superman" it would say 3, for "Bird" 2 etc. If I add another "Superman" it should then change to 4 next to each entry. Also, I will be adding new words all the time, so the function needs to be able to cope with that too.
View 2 Replies
View Related
Jun 6, 2007
I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.
View 9 Replies
View Related
Oct 13, 2007
How do I automatically generate an Entry Number for each entry in a table?
I have a sheet to build up a database of information and a Userform for the data to be input through. Each time the user selects to create a new entry with the userform I'd like to be able to automatically provide a number (i.e. Entry 1 of 12) in the Entry Number field which will then be transfered back to the sheet.
In addition, if an entry is deleted from the sheet the entry numbers beneath the deleted entry will need to update.
View 3 Replies
View Related
Aug 15, 2013
Im building a booking/diary system for work.My system runs 9am till 5pm, 10 min slots. The team is 4 people. So I have 4 columns, one per team member, and each row is 10 mins. 9am, 9.10, 9.20 and so on.
I want to limit it so that if say 2 people already have something in at a time it wont let another person type in that slot. I would prefer a none VBA sollution if possible due to the ancient computers in running it on, but not too fussed if thats the route I go down (as I suspect I will)
The sollution I was considering was having a COUNTA for each range, and if it is 2 or over, not let people type into the other slots. This would also let me add exceptions by using a COUNTIF to minus other characters if needed, but im not sure if I can make excel "block out" a time slot. My other sollution would be similar, but have them to autopopulate the empty cells with an "X" if they are 2 or over as thats what I use to block out slots (breaks, lunches etc)
My other problem is that if they then type into the cell, it will obviously type over the formula, which makes me think it will have to be VBA.
View 2 Replies
View Related
Jul 17, 2009
how to calculate the number of unique entries in a column? I have a column of email addresses and i would like to know how many of them are unique.
View 9 Replies
View Related
Oct 6, 2009
I'm trying to make my formula work. It is working now but I need to add one more criteria and I can' figure it out. I need to get a number of entries that fall into this categories:
John Smith09/02/2009Closed
=sumproduct(--(A1="John Smith"),--(B2>=Today()-90),--(C3="Closed"))
Now I need to get a count of entry either if there is a date >= Today()-90 or if it says "Closed" in C1. But I only need one of the two and I can' figure it out.
View 9 Replies
View Related
Sep 15, 2009
I would like to count number of approved projects (Yes) and denied projects (No) on monthy basis. See attached file for more info..
If F17 is "No" on the month of Sept 09, then cell "O2" should count number of "No" in the month of sept.
View 8 Replies
View Related
Mar 4, 2008
I have a long list of items. I would like to assign the same number to each of the replicates. I've been trying to expand on this: = COUNTIF($A$1:A1,A1). which increments the number for each replicate, but don't seem to be getting anywhere. The attached file might make it clearer what I'm after.
View 3 Replies
View Related
Dec 8, 2012
Basically in cells b12:b26 (15 cells) I need to enter the type of item (which is limited to 2 items). I made a drop down list for that range which works perfectly, therefore the user can choose either item 1 or item 2 or blank. However, I need to figure out a way so that item 1 can be chosen a maximum of 10 times in the range, and item 2 can only be chosen 5 times in that same range..
I am assuming that you can't add that condition to the drop down list... So I was thinking of adding an extra column duplicating b12:b26 (c12:=b12 etc...)- and entering a data validation there.
I was going to enter the following:
if item 1 is chose : =NOT(COUNT($c$12:$c$26)>10) &
if item 2 is chosen: =NOT(COUNT($c$6:$c$6)>5).
However, for some reason it doesn't work - it doesn't block the entry if I try to enter 11 item 1s or 6 item 2s.
View 2 Replies
View Related
Feb 28, 2009
I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).
View 5 Replies
View Related
Dec 2, 2013
I have 7 columns x 3 rows with unique numbers. I wish to determine every combination of the above numbers without repeats.
5
11
17
24
29
35
40
[Code] ........
Column 1 would only use column 1 numbers.
Column 2 would only use column 2 numbers...etc.
Also have the same for 5 rows of unique numbers.
I've been working on this for 10 years and this is the last step to finalizing the WINNING numbers.
Back checking using the 5 row technique I would have spent 10,000,000.00 playing over 8 years with a winners purse of 33,000,000.00
With the 3 row I would have spent close to 200k and won 2.5 mil.
Only problem is that I don't have the 10 to start with, the 200k is a maybe .
I need to be able to generate the combinations to play the numbers.
View 9 Replies
View Related
Sep 19, 2008
I'm trying to Count the number of Entries on a Sheet that match an Hour. Looking through the availiable functions i found COUNTIFS, which is exactly what I want. However, when I try to compare the Hour values within the COUNTIFS arguments, there is an error.
This is the function that I figured would work here:
=COUNTIFS(HOUR(Sheet1!G:G), HOUR(E6))
which should count all entries in column G where its HOUR matches the HOUR in E6 (all are time format). I do realize that in the example above there is only one comparison made and i'm using COUNTIFS instead of COUNTIF, but i'll be adding other comparisons to it once i get this first comparison working.
View 9 Replies
View Related
Oct 9, 2006
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.
All the cust numbers are unique so would I be able to use a wildcard?
View 4 Replies
View Related
Oct 24, 2006
I have a spreadsheet that has 35000 entries and i am trying to count how many material are assigned to a particular group but cannot get it to work. I have included a test spreadsheet to explain what i am trying to achieve
View 3 Replies
View Related
Jul 5, 2006
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.
Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
View 13 Replies
View Related
Nov 2, 2011
I have a column that is filled with different names. Some names appear more than once. There is one name in fact that appears four times.
I am looking for a formula, or VBA if that's necessary, that will look through the range Y2:Y50, and return a value of 4, because the person that has the most duplicates, her name appears 4 times.
If everyone's name appeared only once, except for a person whose name was in that range twice, then the formula would return a 2.
View 6 Replies
View Related
Jul 24, 2014
Basically I first want to be able define around 100 variables such as:
Dog = 1
Cat = 2
Mouse = 3
And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.
So typing in
Dog
Mouse
Mouse
Cat
Dog
would automatically result in
1
3
3
2
1
In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?
View 1 Replies
View Related
Mar 3, 2007
I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.
Sub HTSoutput1()
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"
Sheets("Nuclei").Select
Range("B10:B17").Select.............
View 9 Replies
View Related
Nov 7, 2007
I am trying to do 2 things involving data validation. 1) Count the number of entries in a data validation dropdown list and display a result. 2) Use an If statement to pull the formatting from a cell into another cell.
An example would be if Cell A1="A", then show the drop down list associated with cell or sheet "!ryanB2". Alternatively, I would like to also display the number of entries in the drop down list.
View 9 Replies
View Related
May 7, 2014
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
View 13 Replies
View Related
May 29, 2014
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
View 9 Replies
View Related
Feb 17, 2010
In column A, I have many entries such as AA-1, AA-2, BB-1, BB-2, AA-3, etc.
How do you count the number of entries for only those beginning with "AA" or beginning with "BB"?
View 3 Replies
View Related
Jan 21, 2009
I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.
View 5 Replies
View Related
Jul 29, 2006
I would like help with a worksheet.
I have a column of Data which is a "material description".
In an adjacent column I have the square meterage of the material.
I would like to total all the square meterage of each material and have it shown in another column.
How can I do this?
I have searched far and wide for an example however I am probably using the wrong terms in my search.
Attached is a sample workbook.
View 9 Replies
View Related
May 16, 2009
I have a row of golf scores "I3:X3" they either have a number value or "DNP".
I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.
View 6 Replies
View Related
Jun 12, 2009
If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)
=COUNTA(D6:D15)
TS
TS
VR
IS
IS
OS
VR
OS
8 (total)
View 7 Replies
View Related
Jun 10, 2009
Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C.
Hope this all makes sense. Please feel free to ask if it is not clear.
View 14 Replies
View Related
