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

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top