E20 with the series of numbers in tenth, and with five numbers on one cell. Between 01-37. How do I write a formula on column R to identify any repeated numbers from E20 with E21? In the chart it shows number 10 is repeated from E21, so I place a check mark on the same row as E20 on column R.
On column S how do I write a formula to identify numbers within E20 to see if there are any consecutive numbers? On E20 there is none, on E21 there is one 10-11, and column S will let me know if there is a consecutive by placing a check mark.
I need to identify repeated code with different text value. The codes in B column appears repeatedly in the list. However some codes have different text value (one time appears with public and another time appears with priv). I would like to identify those codes as 1 if they appear with different text value within the list. I highlighted one code 131775 for reference.
I have the attached table of numbers and I need a formula at the end of each column to identify whether any cells in that column consecutively have numbers in them greater than zero. Ideally by a count of how many cells in the column have consecutive numbers greater than zero (so if there are three 1's in a row and then a zero and then another 2 1's I want it to count 5).Excel Help.xlsx
I have a number and i wanted to convert it to a number or character.
Ex.
if i have value of 7, should be converted to a character of "*", the output will be 7 times of "*" (*******) if value is 10, so it will be 10 times "*". (**********)
how do i copy one column to another one (with formula) that not repeat any number?
i have a list of prices and i need that list in other column with any price repeated.... and theses prices are imported data, so they change, thats way need to be formula =/
I have numbers in a table from A1 to X1. From that table I have selected some numbers in example 1: (44,3,11,20,44,3), in which the number 44 and 3 is repeated twice. What I want to do is to conserve one set of repeated numbers and to change one of the sets picking next number of repeated into the following number. Giving a result as follows (45,4,11,20,44,3) and if possible to put it in ascending order.
The same method applies in Example 2: but instead of 2 it conserves 3 repeated numbers and changes the other 3 into next following number.
I am trying to find out if Sumproduct or Countif will provide me the answer but in vain. In the example of the 2 columns of data, how do I find out the number of one-time (or unique) combined occurences for data in column A and B? In my example the answer should be 5. I do not how to proceed with my Sumproduct formula which gives error. =SUMPRODUCT(($A$1:$A$17="A122")*$B$1:$B$17)
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE? ---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
Basically I want to calculate each team's home winning/drawing/losing streak. Here is a sample of the table (I've simplified it a bit and added fictional results): (See Attached)
So, I want a formula that can tell me a given team's (e.g. Arsenal's) latest result, and how many previous consecutive results of that kind there have been. For example, for Arsenal, the result would be something like "W2" (i.e. Won the last two games). At the moment, the best I can do is to get the TOTAL NUMBER of a given team's latest result using this formula:
I have excel sheets with 71936 rows of data collected ~30 times a second for 40 minutes during and driving simulator drive (for 51 participants! all in separate worksheets). The output I get from the simulator includes speed, lane position etc. I've been averaging the data out into minute blocks for most variables, but what I really need to know is: if they exit the "lane" for more than 3 seconds, and if so, how many times they exit the lane in each minute.
Column A is the minute, column B is their lane position. Lane position ranges from -540 to 540. The middle of the left hand lane is -180 (the left hand lane ranges from -360 to 0 - we drive on the left in Australia). If they exit the left hand side of the lane, the lane position value will be between -360 and-540, and if they exit the right hand side the lane position value is 0 or greater.
A formula in a new column that can give a "1" each time they exit the lane for more than 3 seconds would be great, then I think I can make it sum how many times they do it in each minute.
I have an excel spreadsheet to record employee holiday and sickness figures.
It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.
One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.
Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.
In cells v43:v52 I have data which will change often the data is always either a 1,-1 or a 0, I need a way to count from the bottom up and ignore anything that is not part of the chain. It can also contain 0 in rows that are not populated yet but the 0s will always be on the bottom and never in the middle or top.
I.E. in 3 weeks it could be
1 1 1 0 0 0 0 0 0 0 0 0
I would want the formula to spit out 3 because it was 1 3 times in a row..
Now say in week 5 it is 1 1 1..............................
I have a spread sheet of hourly temperatures which is broken down into year, month, day, time, and temperatures. I would like to find the top ten longest stretches of sub-zero temperatures in number of hours and the time and date in which they started and ended. I have attached a spreadsheet for your perusal..
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.
The values in column BN are text with either of those names.
I need to return two values in cells BO7 and BO8 respectively:
1) The number of consecutive days back from the most current day that have been "Over" the limit
2) The date going from the most current day that the first "Over" occurred
to create with the default excel functions the following calculator. I need to calculate the maximum number of positive numbers which happen in a row and the maximum number of consecutive negative numbers. For example in the following list of numbers there are a maximum of 7 consecutive positive and a maximum of 6 consecutive negative numbers:
I want a function that identify the row number, where the date in column A is the same of the active row, but the value in Column B is equal to a different text e.g.=”zz”?
I'm looking at test results and would like to know how many times in a row correct answers were given before getting one incorrect. The way I have this currently set up, is if a student has the answer correct it returns a "TRUE" value, and a "FALSE" value for an incorrect answer. A typical test may have 50 questions, so I would have either "TRUE" or "FALSE" values in rows A1 through A50.
So I've been trying to figure out a formula that counts the number of times "TRUE" rows repeat 'consecutively' in a given column, in this scenario column A. The formula would need to check the current row and if "TRUE" check the row immediately after, if that row is also "TRUE" then check the row immediately after that, until it runs into a "FALSE" string, then display that total count of consecutive "TRUES" in another cell. I understand there may be duplicate results but I'm only really interested in the longest value, so duplicates can be ignored.
If it makes the formula easier, each possible result can be assigned to it's own cell. Let's assume the test has 50 questions. So if a student got all 50 questions correct the result count would be 50 in cell B50, or if they got 25 correct before the first incorrect answer, cell B25 would say 25, etc. I'm guessing I could then easily query this column for the largest value to come up with what I need at a glance.
i've got a sample database (attached) with a userform for inputting of data. What i want it to do is automatically generate the next number and add it to textbox "our ref" on the userform. the number is in Col C. This is the code i've tried using the code highlighted in red which doesnt work.
A person on this board did this for me with this so far and I need to be able to view more than 16 rows. I would like to view all the rows it finds. I have 62,000 rows on information this far and will exceed 500,000 so you can see my need to see all involved to save hours of sifting through data. I would also like a place up top near the box (column G.H)(3,4,5) another box for me to enter a new number combination to search. Presently I have to go through the data to find the first one which is also time consuming. I would like to be able to put say for example in the area I am mentioning:
What I am trying to is to count the number of times a certain number or character appears (either on its own or in a batch of consecutive cells containing that number/character) in a column.An example might clarify things (for reasons of brevity I will write the columns in rows):
If a column looks like (each 1-digit numbers / characters being a consecutive cell) 0 0 X X 0 0 X and I am counting for X, then I should get 2. If my column is X X 0 X X 0 X 0 0, then I should get 3. If my column is 0 X X 0 0 X 0 then I should get 2. If my column is X X 0 X X 0 X then I should get 3. Is there a formula to perform that calculation?
I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
Using a Dynamic Named Range "Data", I would like a Formula to return the Row Number of the "LAST" instance of a duplicate numeric value - repeating twice consecutively in the same column.
I'm trying to create a formula (in Sheet 1) that will return the most recent number of cells containing a value (i.e. not empty) in Sheet 2 Range AD2:A367 greater than zero.
I've tried using COUNTIF but that only adds the individual values, it doesn't 'start from zero again' when a zero value appears.
how to built a formula or a VBA code which would tell me how many times a range of a certain amount of consecutively stnading cells in a given row there is present, where each of the cells belonging to these five cells fulfills a certain criterion.
Example:
How many times does the range of 5 consecutive cells appear in range A1 to P2, where every cells belongng to these 5 consecutive cells would have a value graeater than 10.
After looking at other similar post titles I could not find a formula to work exactly proper.
I have attatched a sample of my problem. What I need is for cells E4:J4 to look for a match in cells B4:B16 and give a value of " Yes or No " in cell K4 if a match is found. Then copy down formula through E5:J5 to B4:B16 - E9:J9 to B4:B16. As my examples indicate Cells K4 would = yes, K5 = yes, K6 = yes, K7 = no, K8 = no, K9 = yes. Column D is the results, Columns E-J represents all combinations of column D.
I have tried the following formulas but they did not work entirely correct.
