Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Series in Excel

A series (sequence) of numbers or values in the range of cells

What is the a series of numbers? Science today recognizes several types of series of numbers. We are interested in Excel for "arithmetic sequence of numbers".  By definition follows:

Arithmetic series, a series of numbers whose members meet the requirements
and(n+1)-an=a(n+2)s(n+1)

eg.
1, 3, 5, 7, etc.
1, 2, 3, 4, 5, etc.

In Excel, the a series of numbers we consider all the numbers that are entered into Excel cells in a sequence. This series of numbers can be defined condition, but does not have to be. Thus, for a series of numbers we consider the chance occurrence of numbers some undefined condition or sequence that can occur in an Excel column or row. A series of numbers can be
  • ascending series of numbers or values
  • descending sequence of numbers or values
  • random series of numbers or values
  • mixed set of numbers or values
  • a continuous string of numbers or values
  • uninterrupted series of numbers or values
  • break down the numbers or values
  • ascending or descending sequence of letters
  • etc.

A series of numbers in an Excel column or row

When it comes to an uninterrupted series of numbers, then immediately we have to know that there is a requirement, which we have to adhere to. For example, we can set a condition for continuing an unbroken series of the largest or smallest numbers in a column or row of Excel.
In the picture below you will see some string of numbers and letters that we have in the column or row Excel.
  • Column A - ascending series of arithmetic numbers
  • Column C - a descending series of arithmetic numbers
  • Column E - series of random (mixed) arithmetic numbers
  • Column G - continuous uninterrupted series of X numbers with the highest sum
  • column I - continuous uninterrupted series of X numbers with the smallest sum
  • Column K - continuous uninterrupted series of the same numbers
  • Column M - continuous uninterrupted string that appears most times
  • Column A - ascending a series of letters of the alphabet
  • Column Q - descending a series of letters of the alphabet
A series or sequence of numbers or letters in Excel

The largest sum of continuous series of numbers in a column of Excel

In the first example, I'll take it mixed or random arithmetic progression of numbers in column 'E'. In this example, I will try to show how we can sum up one continuous string of numbers that have the largest sum in the column. In this case, the largest sum of the numbers is 96 to one continuous and uninterrupted series of groups of 10 numbers out of 24 numbers in the column.

The formulas in cells in the picture below are the following:

The formula in cell B1: =SUMPRODUCT(MAX(A2:A19+A3:A20+A4:A21+A5:A22+A6:A23))

ARRAY formula in cell D2: =MAX(SUMIF(OFFSET($A$2;ROW(INDIRECT("1:"&COUNTA(A:A)-4))-1;0;5);">0"))

ARRAY formula in cell F2: =MAX(SUBTOTAL(9;OFFSET(A2;ROW(A2:A20)-ROW(A2);0;5;1)))

ARRAY formula in cell H2: =MAX(A2:A17+A3:A18+A4:A19+A5:A20+A6:A21)

If you want to have the smallest sum of the column, for a continuous series of uninterrupted numbers, then use instead of the MAX function, you use the MIN function.

=SUMPRODUCT(MIN(A2:A19+A3:A20+A4:A21+A5:A22+A6:A23))

The largest sum of continuous series of numbers

To visually we know that all the numbers involved in the largest sum, we can use a VBA macro that will select a set of numbers that have the greatest sum. (It depends on how much we have filled cell in the column). After starting the VBA macro, you notice selected numbers in sequence. The VBA macro is related to cell B1.

Sub MaxSumGroupNum()
Dim StartRng As Range
Dim EndRng As Range
Dim Sumarum As Long
Dim TestTotal As Long
 Sumarum = Range("B1")
    Set StartRng = Range("A1")
    Set EndRng = StartRng
        Do Until False
       TestTotal = Application.Sum(Range(StartRng, EndRng))
   If TestTotal = Sumarum Then
                  Range(StartRng, EndRng).Select
                  Exit Do
                  ElseIf TestTotal > Sumarum Then
         Set StartRng = StartRng(2, 1)
         Set EndRng = StartRng
                  Else
         Set EndRng = EndRng(2, 1)
   If EndRng.Value = vbNullString Then
                 MsgBox "Sorry, No series found"
                    Exit Do
              End If
         End If
    Loop
End Sub


A similar example you see in the picture below. Of course, the formula in cell B1, you can share it with the number of cells (5), but that VBA macro will not select cells that have numbers in the calculation.

The formula in cell B1: =SUMPRODUCT(MAX(A2:A19+A3:A20+A4:A21+A5:A22+A6:A23))

The formula in cell D1: =B1/5

The largest sum of 5 numbers in sequential and continuous row

The largest number of repetitions of continuous sequence in Excel column

If we want to find the largest number of repetitions of a number in consecutive series and count the number of times the respective number in the sequence consecutive repeats then we can do this by using several formulas as shown below.

You note in the figure below, the number 7 in the excel cell which is a condition in this example. We want to know how many times the most repeated number 7 but in a continuous unbroken series of consecutive numbers. That we do not write the formulas number 7. I've put in a cell 'D1' that we can easily modify if you want the result to another number as a condition.

So our series of numbers is in column 'A'. In the column 'B', I made counting the number of appearances for 7.

The formula in cell B2: =IF(A2=$D$1;1;"")

The formula in cell B2 is copied to the last row:
=IF(AND(ISNUMBER(B2);A3=$D$1);B2+1;IF(A3=$D$1;1;""))

Also, counting how many times appears a number we can do this using the following formula

The formula in cell F3 copied to the last row: =IF(A3=A2;B2+1;1)

How many times a number appears in a row

The largest number of occurrences of the value in a continuous series of Excel columns

If you want to find the largest number of occurrences of a number or value of the successive and continuous series then use the following formula. Column 'A' is our set of numbers or values.

The formula in cell B2: =IF(OR(AND(A2=9;A3=9);AND(A2=9;A3=0));1+B1;0)

The formula in cell E2 finds the largest number or the value of the auxiliary column 'B': =MAX(B2:B27)

ARRAY formula in cell E4 finds the largest number or value in column 'A', then do not use extra column:

=MAX(FREQUENCY(IF(A2:A27=9;ROW(A2:A27));IF(A2:A27<>9;ROW(A2:A27))))

How many times the value is repeated in consecutive series by groups

How to count the group of a series of numbers and how many there are in the group of successive sets of numbers

In this example the tutorial, I will show how we can count the group of a series of consecutive numbers or values. You notice in the image below in the column 'A' mixed series of numbers. In this mixed series, we have a certain group of a series of numbers. Our goal is to count how much times it appears consecutive series of 3 numbers in the group as well as the maximum number of digits in a consecutive series.

ARRAY formula in cell C2 count the number of times that number 1 appears in the group successive continuous and uninterrupted series, look for the group with the largest number of occurrence value or the number 1

=MAX(SUBTOTAL(9;OFFSET(A2;ROW(A2:A20)-ROW(A2);0;5;1)))

ARRAY formula in cell C2 finds how much in the column has a group of successive sets of numbers with the number 1 or so that the group contains 3x number 1

=SUM(IF(FREQUENCY(IF(A2:A25=1;ROW(A2:A25));IF(A2:A25<>1;ROW(A2:A25)))=3;1))

How much is the most specific numbers in consecutive series

How many times text appears in consecutive uninterrupted series

If you want to count cells that contain text and how many times the same text appears in a consecutive sequence then use the following formula

The formula in cell B2 count the number of repeats in the order of appearance, copy the formula to the last row:

=IF(A2=A1;B1+1;1)

The formula in cell E2 is related to the requirement of 'D' column asking for the maximum number of counts, that is repetitions of a certain letter or text. Copy a formula down.

=MAX(($A$2:$A$23=D2)*($B$2:$B$23>1)*($B$2:$B$23))

How many times text appears in consecutive uninterrupted series