Repeat values across cells

 

1. Repeat values across cells

This article explains how to repeat specific values based on a table, the table contains the items to be repeated and how many times they will be repeated.

The array formula in cell A6 utilizes the table and repeats the values until the condition is met.

There is also a section below that explains how to repeat values in sequence based on corresponding numbers.

Array formula in cell A6:

How to create an array formula

1. Double press with left mouse button on cell A6 so the prompt appears.
2. Copy and paste the array formula above to the cell.
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys.

You can check using the formula bar that you did above steps right, Excel tells you if a cell contains an array formula by surrounding the formula with a beginning and ending curly brackets, like this: {=array_formula}.

Don't enter these characters yourself they show up automatically if you did the above steps correctly.

Explaining array formula in cell A6

I recommend that you use the built-in "Evaluate Formula" feature in Excel to better understand and troubleshoot formulas, it is a great tool that allows you to see each calculation step.

Go to tab "Formulas" on the ribbon, then press with left mouse button on "Evaluate Formula" button. Press with left mouse button on "Evaluate" button to move to next calculation step, press with left mouse button on "Close" button to dismiss the dialog box.

Step 1 - Count previous values

The COUNTIF function counts values in a cell range based on a condition, however, in this case, I use a growing cell reference that expands when you copy the cell and paste to cells below.

This will make the COUNTIF function count based on multiple conditions, this technique is what makes you required to enter the formula as an array formula.

This way the function keeps track of previous values above the current cell so the correct number of values is returned based on the table.

COUNTIF($A$5:A5,$A$1:$A$4)

COUNTIF(range, criteria)

The first argument is $A$5:A5 which is a cell reference, however, it contains two parts. The first part is an absolute cell reference pointing to A5, you can tell that it is absolute by the $ characters.

It means that the cell reference is locked to cell A5, both the column and row number is, locked, and does not change when you copy the cell to cells below. There are exceptions to this like inserting new rows or columns.

The second part is a relative cell reference and this changes when you copy the cell and paste to cells below which is great because the cell reference expands and takes multiple cells into account.

For example, in cell A6 the cell reference is $A$5:A5, however, in cell A7 the cell reference changes to $A$5:A6 and this makes the formula aware of previous returned values above the current cell.

COUNTIF($A$5:A5,$A$1:$A$4)

becomes

COUNTIF("",{"A";"B";"C";"D"})

and returns {0;0;0;0}

In cell A6 there are no previous values above, only cell A5 which is empty.

The COUNTIF function returns an array containing the same number of values as the table and each value in the array represents how many times the values in the table have been displayed.

An array uses commas and semicolons to separate values, commas are between columns and semicolons are between rows.

Step 2 - Compare array values to list

This step checks if the values in the array is equal to the numbers in the table, this works because the values in the array correspond to the numbers in the table.

The equal sign compares the returned values from the COUNTIF function with the values in cell range B1:B4 and returns TRUE or FALSE. Note that the cell reference to B1:B4 is absolute. We don't want it to change when the cell is copied to cells below.

COUNTIF($A$5:A5,$A$1:$A$4)=$B$1:$B$4

becomes

{0;0;0;0}={5;2;1;4}

and returns {FALSE;FALSE;FALSE;FALSE}

Not a single value meets the criteria in the table in cell A6, that is why all values in the array are FALSE.

Step 3 - Match first FALSE value in array

The MATCH function returns the position of a given value in a cell range or array.

MATCH(lookup_value, lookup_array, [match_type])

The first argument is FALSE, we want to know the position of the first value that has not been repeated the number of times specified in the table.

The second argument is the array we created in step 2 and the third argument is 0 (zero) which means we want an exact match.

MATCH(FALSE, COUNTIF($A$5:A5,$A$1:$A$4)=$B$1:$B$4,0)

becomes

MATCH(FALSE, {FALSE;FALSE;FALSE;FALSE},0)

and returns 1.

Step 4 - Return a value of the cell at the intersection of a particular row and column

The INDEX function returns a value from a cell range or array based on a row and column number. The column number is optional if the cell range is only a single column.

INDEX($A$1:$A$4, MATCH(FALSE, COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, 0))

becomes

=INDEX($A$1:$A$4, 1)

and returns A in cell A6.

Repeat values in a predetermined series

This example demonstrates a formula that repeats values in a given sequence and also how many times each value is to be repeated.

Array formula in cell A6:

Explaining formula in cell A6

Step 1 - Count previous values

COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4

becomes

{0;0;0;0}=$B$1:$B$4

becomes

{0;0;0;0}={5;2;1;4}

and returns

{FALSE;FALSE;FALSE;FALSE}.

Step 2 - Replace values to be repeated with their count

IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4))

becomes

IF({FALSE; FALSE; FALSE; FALSE}, "", {0;0;0;0})

and returns

{0;0;0;0}.

Step 3 - Find smallest count number

MIN(IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)))

becomes

MIN({0;0;0;0})

and returns 0 (zero).

Step 4 - Find relative position

MATCH(MIN(IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4))), IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)), 0)

becomes

MATCH(0, IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)), 0)

becomes

MATCH(0, {0;0;0;0}, 0)

and returns 1.

Step 5 - Return value based on position

INDEX($A$1:$A$4, MATCH(MIN(IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4))), IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)), 0))

becomes

INDEX($A$1:$A$4, 1)

and returns "Z" in cell A6.

3. Find the most/least consecutive repeated value - VBA

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show you how to build a simple UDF that will simplify these formulas significantly.

The following user-defined function returns the size of each consecutive sequence in a range. The udf is entered in cell range B2:B25, see this picture.

The UDF returns an array with the same size as the argument range. The cell range has 24 cells and the array has 24 values.

3.1. VBA code

'Name User Defined Function
Function CountConsVal(r As Range)

'Dimension variables and declare data types
Dim i As Long, s As Long

'Save value in cell range r to variable Rng
Rng = r.Value

'Iterate through values in array variable Rng
For i = LBound(Rng, 1) To UBound(Rng, 1) - 1

    'Check if next value is equal to current value
    If Rng(i, 1) = Rng(i + 1, 1) Then

        'Add 1 to numbers stored in variable s
        s = s + 1

        'Clear container in array variable Rng
        Rng(i, 1) = ""

    'Go here if next value is NOT equal to current value
    Else
  
        'Add 1 to variable s and save to container in array variable Rng
        Rng(i, 1) = s + 1

        'Save 0 to variable s
        s = 0
    End If
Next i

'Add 1 to variable s and then save result to the last container in array variable Rng
Rng(UBound(Rng), 1) = s + 1

'Return values to worksheet
CountConsVal = Rng
End Function

Back to top

3.2. Array formulas

Array formula in cell E3:

Array formula in cell E4:

Array formula in cell G3:

Array formula in cell G4:

Array formula in cell E7:

Array formula in cell E8:

Back to top

3.3. Where to put the code?

To build a user-defined function in your workbook, follow these steps:

1. Press Alt + F11 to open the visual basic editor.
2. Press with left mouse button on "Insert" on the menu.
3. Press with left mouse button on "Module".
   
4. Copy the code above and paste it to the code module.

Back to top

3.4. Explaining the User Defined Function

3.4.1 Function name and arguments

A user-defined function procedure always starts with "Function" and then a name. This UDF has a single argument r. Variable r is a range.

Function CountConsVal(r As Range)

3.4.2 Declaring variables

Variable i and s are declared data type Long. Read more about Defining data types.

Dim i As Long, s As Long

3.4.3 Transfer values from r (range) to a Rng (variant) array

Rng = r.Value

3.4.4 For ... Next statement

Repeats a group of statements a specified number of times. LBound returns the lower bound of an array and UBound the upper bound.

LBound(array, dimension) UBound(array, dimension), the dimension argument can be omitted if the array is a one-dimensional array. In this case the Variant Rng is a two-dimensional array despite the fact that the range r is a one-dimensional array. UBound(Rng, 1) returns the number of rows in Rng array.

For i = LBound(Rng, 1) To UBound(Rng, 1) - 1

...

Next i

3.4.5 If ... then... Else ... End If

Check if the current value (i) is equal to the next value (i+1)

If Rng(i, 1) = Rng(i + 1, 1) Then

3.4.6 Add number 1 to variable s

s = s + 1

3.4.7 Delete value in array

Rng(i, 1) = ""

3.4.8 Assign a value (s + 1) to array variable Rng(i, 1)

The current value in the array is equal to s + 1

Rng(i, 1) = s + 1

3.4.9 Assign 0 (zero) to variable s

s = 0

3.4.10 Assign a value (s + 1) to the last value in the array Rng(UBound(Rng), 1)

Rng(UBound(Rng), 1) = s + 1

3.4.11 The udf returns an array of values

CountConsVal = Rng

3.4.12 End a udf

A function procedure ends with this statement.

End Function

Back to top

4. How to count repeating values

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent.

4.1. How to count any contiguous value

The formula in cell D4 checks if the value in cell D3 is not equal to the value in cell D4. If true (not equal) the result is 1. If false (equal) the formula adds 1 to the previous value above.

Copy cell D4 and paste to cells below as far as needed in order to count all values in column B.

Formula in cell D4:

The formula in cell F3 simply extracts the largest number from cell range D3:D14. Adjust cell ref D3:D14 if you have more values than the example shown in the image above.

Formula in cell F3:

Back to top

Explaining formula in cell D4

Step 1 - Logical test

Combine the less than character and the larger than character and you get not equal to. The result is a boolean value TRUE or FALSE.

The first argument in the IF function is the logical test, it determines what value to return.

B4<>B3

becomes

"C"<>"A"

and returns TRUE.

Step 2 - Evaluate IF function

The IF function returns one value if the logical test returns TRUE and another value if the logical test returns FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(B4<>B3, 1, D3+1)

becomes

IF(TRUE, 1, D3+1)

and returns 1 in cell D4.

Back to top

4.2. How to count a specific contiguous value

Thanks, David.

 

Formula in cell D4:

Explaining formula in cell D4

Step 1 - Logical test

Combine the less than character and the larger than character and you get not equal to. The result is a boolean value TRUE or FALSE.

The first argument in the IF function is the logical test, it determines what value to return.

B4<>B3

becomes

"N/A"<>"N/A"

and returns FALSE.

Step 2 - Evaluate IF function

The IF function returns one value if the logical test returns TRUE and another value if the logical test returns FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(B4<>B3, 1, D3+1)

becomes

IF(FALSE, 1, D3+1)

becomes

D3+1

becomes

"" + 1

and returns 1.

Step 3 - Nested IF function

The second IF function makes sure that the value in cell B4 is equal to 1, we want to only count cells equal to 1.

IF(B4=1, IF(B4<>B3, 1, D3+1), "")

becomes

IF("N/A"=1, IF(B4<>B3, 1, D3+1), "")

becomes

IF(FALSE, IF(B4<>B3, 1, D3+1), "")

becomes

IF(FALSE, 1, "")

and returns "" (nothing) in cell D4.

Back to top

Back to top

Read Rick Rothsteins's comment below.

5. Extract the most repeated adjacent values in a column

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell D4 returns the value of the longest consecutive sequence in column A.

Cell F3 returns the length of the shortest consecutive sequence of a value in column A and cell F4 returns the value of the smallest consecutive sequence in column A.

5.1. Length of the most repeated grouped value

The array formula in cell D3 calculates how many cells contain the same given value repeated. It returns four in cell D3 because value "B" is repeated four times and is adjacent to each other in cell range A11:A14.

Array formula in cell D3:

Back to top

How to enter an array formula

1. Select cell
2. Copy/Paste the formula to the formula bar
3. Press and hold CTRL + SHIFT simultaneously
4. Press Enter once
5. Release all keys

Update 3-18-2021, Excel 365 formula:

Explaining formula in cell D3

Step 1 - Compare values in cell ranges

The less than and the greater than characters combined checks if a value is not equal to another value, it returns TRUE if not equal and FALSE if equal.

The second cell range is offset by 1, this makes the comparison check the following value which makes it easy to calculate the number of repeated values in a column.

$A$2:$A$25<>$A$3:$A$26

becomes

({"B"; "B"; "C"; "C"; "B"; "B"; "C"; "B"; "C"; "B"; "B"; "B"; "B"; "C"; "B"; "A"; "C"; "A"; "C"; "C"; "A"; "C"; "C"; "C"}<>{"B"; "C"; "C"; "B"; "B"; "C"; "B"; "C"; "B"; "B"; "B"; "B"; "C"; "B"; "A"; "C"; "A"; "C"; "C"; "A"; "C"; "C"; "C"; 0}

and returns

{FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}

Step 2 - Replace TRUE with corresponding row number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24})

and returns

{1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}

Step 3 - Calculate frequency

The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers. It returns an array that is one more item larger than the bins_array.

FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$25, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)) , ""))

becomes

FREQUENCY({1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}, {""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24})

and returns

{1; 1; 1; 0; 0; 0; 3; 0; 0; 0; 0; 0; 1; 0; 2; 0}

Step 4 - Calculate max number in the array

The MAX function returns the largest number in a cell range or array.

MAX(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$25, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)) ,""))+1)

becomes

MAX({1; 1; 1; 0; 0; 0; 3; 0; 0; 0; 0; 0; 1; 0; 2; 0}+1)

becomes

MAX({2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1})

and returns 4.

Back to top

5.2. Extract most repeated grouped value

The image above demonstrates a formula in cell D4 that extracts the most repeated adjacent value in column A.

Array formula in cell D4:

Update 3-18-2021, Excel 365 formula in cell D4:

Back to top

Explaining formula in cell D4

Step 1 - Compare cell ranges and return corresponding row number if TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), "")

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24}, "")

and returns

{""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24}

Step 2 - Compare cell ranges and return corresponding row number if FALSE

IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)))

becomes

IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, "", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24})

and returns

{1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}

Step 4 - Calculate the frequency of repeated values in column A

The FREQUENCY function calculates how often values occur within a range of values and returns a vertical array of numbers. It returns an array that is one more item larger than the bins_array.

FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1

becomes

FREQUENCY({1; ""; 3; ""; 5; ""; ""; ""; ""; 10; 11; 12; ""; ""; ""; ""; ""; ""; 19; ""; ""; 22; 23; ""}, {""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24})+1

becomes

{1; 1; 1; 0; 0; 0; 3; 0; 0; 0; 0; 0; 1; 0; 2; 0} + 1

and returns

{2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1}

Step 5 - Calculate largest value in array

The MAX function returns the largest number in a cell range or array.

MAX(FREQUENCY(y, x)+1)

becomes

MAX({2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1})

and returns 4.

Step 6 - Find the position in array

The MATCH function returns a number representing the relative position af a given value in a cell range or array.

MATCH(MAX(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)),""), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1), FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)),""), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 0)

becomes

MATCH(4, {2; 2; 2; 1; 1; 1; 4; 1; 1; 1; 1; 1; 2; 1; 3; 1}, 0)

and returns 7.

Step 7 - Calculate k-th smallest value

The SMALL function returns the k-th smallest number from a cell range or array.

SMALL(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""), MATCH(MAX(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)),""), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1), FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)),""), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 0))

becomes

SMALL({""; 2; ""; 4; ""; 6; 7; 8; 9; ""; ""; ""; 13; 14; 15; 16; 17; 18; ""; 20; 21; ""; ""; 24}, 7)

and returns 13.

Step 8 - Return value

The INDEX function returns a value from a given cell range or array based on a row and column number.

INDEX($A$2:$A$25, SMALL(IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""), MATCH(MAX(FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1), FREQUENCY(IF($A$2:$A$25<>$A$3:$A$26, "", MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25))), IF($A$2:$A$25<>$A$3:$A$26, MATCH(ROW($A$2:$A$25), ROW($A$2:$A$25)), ""))+1, 0)))

becomes

INDEX($A$2:$A$25, 13)

and returns "B" in cell D4.

Back to top

5.3. Length of the shortest consecutive sequence of a value

Array formula in cell F3:

Back to top

5.4. Value of the shortest consecutive sequence

Array formula in cell F4:

Back to top

5.5. Length of second longest consecutive sequence of a value

Array formula in cell D7:

Back to top

5.6. Value of the second-longest consecutive sequence

Array formula in cell D8:

If you are interested in learning and constructing array formulas, check out my Advanced excel course.

Back to top

6. Count groups of repeated values per row

Answer:

I am using a much smaller size to more easily show how the formulas work and what the functions used return.

There are 14 groups of value 0 (zero) per row in cell range B2:G8 and 12 groups of value 1. The image above shows 14 blue circles around each group of value 0 to demonstrate how the formula below works.

Regular formula in cell B11:

Array formula in cell C11:

This formula is different because Excel evaluates a blank cell as 0 (zero), this makes it impossible to use the same formula as in cell B11.

How to create an array formula

1. Copy formula above.
2. Select cell C11 with mouse.
3. Paste formula (keyboard shortcut CTRL + v)
4. Press and hold CTRL + SHIFT simultaneously.
5. Press Enter once
6. Release all keys.

Explaining array formula in cell C11

The formula in cell C11 is slightly more complicated than the formula in cell B11, they are however so similar that I will only explain the formula in cell C11.

Step 1 - Check if cell is blank

The ISBLANK function returns TRUE if the cell is blank and FALSE if not blank, no surprises here.

ISBLANK(A2:F8)

becomes

ISBLANK({0, 1, 1, 1, 1, 0; 0, 0, 1, 0, 0, 1; 0, 0, 1, 0, 0, 0; 0, 0, 1, 1, 0, 1; 0, 0, 0, 0, 1, 0; 0, 0, 1, 0, 0, 1; 0, 1, 1, 1, 1, 1})

and returns

{TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE}.

Step 2 - If blank return a blank

The IF function returns a blank if the logical expression ISBLANK(A2:F8) returns TRUE and returns the number itself if FALSE.

IF(ISBLANK(A2:F8),"",A2:F8)

becomes

IF({TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE},"",A2:F8)

becomes

IF({TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE; TRUE, FALSE, FALSE, FALSE, FALSE, FALSE}, "", {0, 1, 1, 1, 1, 0; 0, 0, 1, 0, 0, 1; 0, 0, 1, 0, 0, 0; 0, 0, 1, 1, 0, 1; 0, 0, 0, 0, 1, 0; 0, 0, 1, 0, 0, 1; 0, 1, 1, 1, 1, 1})

and returns

{"",1,1,1,1,0;"",0,1,0,0,1;"",0,1,0,0,0;"",0,1,1,0,1;"",0,0,0,1,0;"",0,1,0,0,1;"",1,1,1,1,1}

These two steps are necessary due to Excel not being able to compare a 0 (zero) with an empty cell. Excel evaluates an empty cell as 0 (zero).

Step 3 - Compare arrays

This step compares values in order to identify groups of repeated values.

(B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))

becomes

(B2:G8<>{"",1,1,1,1,0;"",0,1,0,0,1;"",0,1,0,0,0;"",0,1,1,0,1;"",0,0,0,1,0;"",0,1,0,0,1;"",1,1,1,1,1})

becomes

({0, 1, 1, 1, 1, 0; 0, 0, 1, 0, 0, 1; 0, 0, 1, 0, 0, 0; 0, 0, 1, 1, 0, 1; 0, 0, 0, 0, 1, 0; 0, 0, 1, 0, 0, 1; 0, 1, 1, 1, 1, 1}<>{"",1,1,1,1,0;"",0,1,0,0,1;"",0,1,0,0,0;"",0,1,1,0,1;"",0,0,0,1,0;"",0,1,0,0,1;"",1,1,1,1,1})

and returns

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE; TRUE, TRUE, TRUE, FALSE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE, FALSE, TRUE; TRUE, TRUE, FALSE, TRUE, TRUE, FALSE; TRUE, FALSE, FALSE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}

Step 4 - Check if cell is 0 (zero)

This step filters groups of zeros, we don't want to count 1's.

B2:G8=0

becomes

{0, 1, 1, 1, 1, 0; 0, 0, 1, 0, 0, 1; 0, 0, 1, 0, 0, 0; 0, 0, 1, 1, 0, 1; 0, 0, 0, 0, 1, 0; 0, 0, 1, 0, 0, 1; 0, 1, 1, 1, 1, 1}=0

and returns

{FALSE, FALSE, FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE, FALSE, TRUE; TRUE, FALSE, TRUE, TRUE, TRUE, FALSE; TRUE, FALSE, FALSE, TRUE, FALSE, FALSE; TRUE, TRUE, TRUE, FALSE, TRUE, FALSE; TRUE, FALSE, TRUE, TRUE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE, TRUE}

Step 5 - Multiply arrays

(B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))*(B2:G8=0)

becomes

{TRUE, FALSE, FALSE, FALSE, TRUE, FALSE; TRUE, TRUE, TRUE, FALSE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE, FALSE, TRUE; TRUE, TRUE, FALSE, TRUE, TRUE, FALSE; TRUE, FALSE, FALSE, TRUE, TRUE, TRUE; TRUE, TRUE, TRUE, FALSE, TRUE, TRUE; TRUE, FALSE, FALSE, FALSE, FALSE, TRUE}*{FALSE, FALSE, FALSE, FALSE, TRUE, TRUE; TRUE, FALSE, TRUE, TRUE, FALSE, TRUE; TRUE, FALSE, TRUE, TRUE, TRUE, FALSE; TRUE, FALSE, FALSE, TRUE, FALSE, FALSE; TRUE, TRUE, TRUE, FALSE, TRUE, FALSE; TRUE, FALSE, TRUE, TRUE, FALSE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE, TRUE}

and returns

{0, 0, 0, 0, 1, 0; 1, 0, 1, 0, 0, 1; 1, 0, 1, 0, 0, 0; 1, 0, 0, 1, 0, 0; 1, 0, 0, 0, 1, 0; 1, 0, 1, 0, 0, 1; 0, 0, 0, 0, 0, 1}.

Step 6 - Sum values

The SUM function adds the numbers in the array and returns a total.

SUM((B2:G8<>IF(ISBLANK(A2:F8),"",A2:F8))*(B2:G8=0))

becomes

SUM({0, 0, 0, 0, 1, 0; 1, 0, 1, 0, 0, 1; 1, 0, 1, 0, 0, 0; 1, 0, 0, 1, 0, 0; 1, 0, 0, 0, 1, 0; 1, 0, 1, 0, 0, 1; 0, 0, 0, 0, 0, 1})

and returns 14 in cell C11.

Sequence category

More than 1300 Excel formulas

Excel categories

Comments (39)