consecutive numbers

Hi everyone, I am trying to find consecutive numbers with largest value. I have used the formula then did not worked exactly like I want: =MAX(FREQUENCY(IF(A2:A26>0,ROW(A2:A26)),IF(A2:A26<=0,ROW(A2:A26)))) This formula return longest sequence of numbers, not consecutive numbers with largest...

Hi everyone. I have a number spreadsheet with 20 columns /100 rows of numbers. I need to get the sum of the highest 4 CONSECUTIVE numbers of each columns. I have found two formula to get the sum: =SUMPRODUCT(MAX(D3:D95+D4:D96+D5:D97+D6:D98)) or =MAX(INDEX(D3:D95+D4:D96+D5:D97+D6:D98,0))...

G'day Everyone, I hope you can help me with a counting issue. I have a spreadsheet that tracks project status changes that's updated weekly. Every week project managers show their overall project status using a RAG status. The colours correspond to numbers as below. Green Amber Red I want...

Hi Guys, Hope you can help! If you guys could help me with this I would be hugely grateful as this would save me sooo much time every month. Basically, I want to count the number of times 1 appears in 3 or more consecutive cells. Each cell only contains a 1 or 0. <tbody> Name Apr 1st Apr...

Hi all, I've tried searching lots of places for help on this, but cannot find what I need.... I've been given the most unhelpful file and need to try to decipher it to create useful reports. I have lots of data in a single cell, and lots of other cells on the same row contain a similar story. I...

I need to search a text string, find 3 consecutive numbers in a row, then extract the first two characters. I have given an example below. The 3 consecutive numbers will always be between "-" but the occurrence of "-" will vary. The numbers also might be stored as text within the string. The...

In the table below the first column is my data, the second and third are what I want. Any ideas how to do this would be really appreciated.:biggrin: Basically I want to find consecutive blocks of 4 or more numbers within the #N/As and copy these. I then want to use TRIMMEAN on each block to trim...

Hello friends, I have a question that is bothering me for some time now, and it is a bit difficult to explain, hope you will understand:confused:. I have a sheet that looks like the table below. <tbody> 1 7 13 2 8 14 3 9 15 4 10 16 5 11 17 6 12 18 </tbody> Now...

Hello guys, I am using this formula =SUMPRODUCT(--(FREQUENCY(IF(D3:D38>=5,ROW(D3:D38)),IF(D3:D38<=4,ROW(D3:D38)))=5)) which is an array to identify every 5 consecutive numbers above 5. The only problem is if there are more then 5 consecutive numbers and not exact 5 then it wont return...

Hello, I have a rather large dataset, and apart from the top row (which is a header row), all the data is either a 0 or a 1. Just looking at one column, I could have something like: <tbody> A B index data 1 0 2 0 3 1 4 1 5 1 6 0 7 0 8 0 9 1 10 1 11 0 12 1 13 1 14...

<tbody> Row # Value 1 385 2 -732 3 244 4 457 5 436 </tbody> Is there a formula in excel that would identify the consecutive values that yield the highest sum ? In this simple example adding rows 3 to 5 will give you the highest total which is 1,137. I have a list of nearly 1,000...

Trying to find a formula that will average only if EACH cell has a value greater than 0 in it; or if the last 3 consecutive months have a value greater than 0. Any help is greatly appreciated. Thanks

Is there a formula that would search for rows, or cells withing a row, that contain information and populate consecutive numbers in an adjacent column. For example if I am working with rows 1-4... F1 and F4 has information populated, while F2 and F3 are blank. I would like for the formula to...

I am trying to build a formula that will register the number of consecutive days a given task is either completed or not completed. The most recent cell (C1) should show a positive or negative value. A positive value will be the number of consecutive days the task wasc ompleted. A negative...

I am using Excel 2011 for Mac. I have created a template for set of petty cash vouchers fitting 6 to a single page: three down and two across. They are all identically formatted and consistent on each page. There are several pages to each sheet and I have several sheets to a file. I only need...

Hi, I'm working on a catalog index and have product keywords in column A (repeated on additional rows if they reference more than one page), and the page numbers in column B. <TBODY> Keyword Page No. Telephone 350 Telephone 351 Telephone 352 Telephone 400 </TBODY> What...

All, Thank you for your time for looking at my question. I would like to count the # of consecutive cells in a column that have a zero in them if there are more than 10 consecutive cells with a value of zero. For example, I have a spreadsheet that records production data from a machine. Each...

Hi, after some help with identifying data in a large spreadsheet. (i'm using excel 2000 XP i think) I've used a formula i found on this site to identify the count of repeat data i have, which is nicely listed from 1 - about 50. Now i want to identify where there occurs runs of over 6...

I have a list where I need to identify if the TicketNumber cell contains invalid numbers. Examples of valid numbers are: 1563447, VT12645895, VT#8564466. Samples of invalid numbers are: 11111111, 11122222, VT111111, VT1112222, NA or "not available". The alphas are ok only if they appear on a...

I've worked out how to do conditional formatting for highlighting consecutive no's in a range, which is =ISNUMBER(MATCH(1,ABS($AZ1:$BE1-AZ1),0)). Now I can't get my head around how to get the total number of consecutive numbers highlighted in a row so that I can copy/paste the formula for the...