consecutive numbers

  1. F

    Sum of highest 4 consecutive numbers and define the 4 numbers

    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))...
  2. G

    Counting the Most Recent Consecutive Values

    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...
  3. T

    Counting consecutive values that appear a set number of times. CountIf?

    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...
  4. R

    find multiple sets of consecutive numbers in a single cell

    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...
  5. A

    Extract only 3 consecutive numbers from string

    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...
  6. G

    Finding consecutive numbers and then taking average of middle 50%

    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...
  7. S

    Excel Auto Complete Cells until given value

    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...
  8. M

    Return value for every 5 positive numbers in a column !

    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...
  9. S

    Counting each occurrence of consecutive values in a column

    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...
  10. B

    Identifying rows that sum up to the largest total

    <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...
  11. W

    Average or sum a range only if ALL have cell values > 0

    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
  12. D

    Consecutive Numbering based on poplated cells

    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...
  13. T

    Running Tally of Consecutive Yeses and Nos. (Where minus 3, plus 1 = +1)

    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...
  14. R

    formula to Auto Number vouchers throughout XL sheet and across sheets in a single file.

    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...
  15. S

    Book index - need help joining page numbers when consecutive

    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...
  16. N

    count # of cells that are zero if more than 10 consecutive cell have zeros in them (XP, Excel 2010)

    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...
  17. J

    how to identify runs of 6 consecutive numbers in adjacent cells...

    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...
  18. J

    Identifying cells with invalid numbers

    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...
  19. H

    Counting total of consecutive no's for a row.

    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...

Some videos you may like

This Week's Hot Topics

Top