Ace My Paper - get your Excel tasks done by experts.

Assignmentgeek.com - makes your homework shine. # 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 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 ## 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)) 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 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) ## 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 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 Excel 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 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)) 