Play-It-Through Chess Game in a Single Google Sheets Formula

Play It Through Chess Game in Google Sheets Formula

In 1956, a 13-year old Bobby Fischer announced his chess genius to the world, winning one of the finest games in chess history. In fact, Chess Review called it “The Game of the Century“.

In a game full of beauty and surprise, Fischer overcame his much older, more experienced opponent, the International Master Donald Byrne.

In this video and blog post, we create a single formula in Google Sheets that plays through this game!


Continue reading Play-It-Through Chess Game in a Single Google Sheets Formula

Build Numbered Lists With The Amazing SEQUENCE Function

The SEQUENCE function is a useful function in Google Sheets. It’s a powerful way to generate numbered lists.

SEQUENCE Function Syntax

=SEQUENCE(rows, columns, start, step)

As arguments for the SEQUENCE function, you specify 1) the number of rows, 2) the number of columns, 3) a start value, and 4) a step size.

Arguments 2, 3, and 4 are optional. However, if you want to set them you need to include the previous ones (e.g. if you want to set a step size in argument 4, then you need to set 1, 2, and 3 as well).

Keep this order in mind as you look through the examples below and you’ll soon understand how the function works.

1. Ascending list of numbers

=SEQUENCE(5)

=SEQUENCE(5)

Continue reading Build Numbered Lists With The Amazing SEQUENCE Function

Recursion in Google Sheets With Formulas

Recursion in Google Sheets is now possible with the introduction of Named functions, LAMBDA functions, and LET functions. In this post, we’ll explore the concept of recursion and look at how to implement recursion in Google Sheets.

Prime Number Distribution
Discovering prime numbers using recursion in Google Sheets
Continue reading Recursion in Google Sheets With Formulas

Formula Challenge #7: Generate A Repeating Sequence Of Numbers

This Formula Challenge originally appeared as Tip #243 of my weekly Google Sheets Tips newsletter, on 27 February 2023.

Congratulations to everyone who took part and submitted a solution!

Sign up for my newsletter to receive future Formula Challenges:

 

Formula Challenge 7 Question:

Can you create a single formula that creates the output shown in this image:

Irregular Sequence

Assume a height of 100 rows.

Solutions

There were a lot of great solutions! Here are 8 of the best:

Solution 1: Text Approach

=FLATTEN(SPLIT(REPT("1,299,13,50,",25),","))

This formula starts with a text representation of the number sequence “1,299,13,50,”.

It uses REPT to repeat the sequence, SPLIT to separate by the commas, and finally FLATTEN (or TRANSPOSE) to convert to a column format.

This is the shortest formula by character count.

Solution 2: IF and SEQUENCE

=ArrayFormula(FLATTEN(IF(SEQUENCE(25),{1,299,13,50})))

In this formula, SEQUENCE generates a column of 25 numbers. An IF formula wrapper then takes each number as an input and, since numbers are truthy values, the IF outputs the array.

The FLATTEN function then stacks into a single column.

Solution 3: MAKEARRAY + CHOOSE

=MAKEARRAY(100,1,LAMBDA(i,_,CHOOSE(MOD(i-1,4)+1,1,299,13,50)))

The MAKEARRAY function generates an array of 100 rows in 1 column. The LAMBDA function uses MOD to create a sequence 1,2,3,4,1,2,3,4,1,2,3,4…

The CHOOSE function converts the 1,2,3,4 into the sequence 1,299,13, or 50.

Also, note the use of the underscore “_” to denote the argument in the LAMBDA that is not used in the formula.

Solution 4: CHOOSE + SEQUENCE

=ArrayFormula(CHOOSE(MOD(SEQUENCE(100)-1,4)+1,1,299,13,50))

The SEQUENCE function generates a list from 1 to 100. MOD converts this to a repeating sequence 1,2,3,4,1,2,3,4,1,2,3,4,…

The CHOOSE function then sets the final values and the whole is wrapped with ArrayFormula to output the full array.

Solution 5: SEQUENCE + Array

=ArrayFormula(FLATTEN(SEQUENCE(25,1,1,0)*{1,299,13,50}))

The SEQUENCE function generates 25 rows of 1’s, which is multiplied by the array literal {1,299,13,50}. This creates 4 columns, one for each number in the sequence. FLATTEN then stacks them in a single column.

Solution 6: Matrix Multiplication

=ArrayFormula(FLATTEN( MMULT(SEQUENCE(25,1,1,0),{1,299,13,50})))

This is essentially the same as the previous solution (#5) but uses the MMULT function to multiply the two arrays.

Solution 7: Array + LEFT trick

=ArrayFormula(1*FLATTEN({1,299,13,50}&LEFT(F1:F25,0)))

This unusual solution uses the LEFT function to create an empty array of 25 rows, because the length is set to 0.. It can use any column except the one containing the formula itself.

Solution 8: SWITCH + ROW

=ArrayFormula(
SWITCH(MOD(ROW(A1:A100),4),
1,ROW(),
2,ROW()+298,
3,ROW()+12,
ROW()+49))

This novel approach uses MOD with the ROW function to generate a repeating sequence. This is fed into a SWITCH function to convert to the desired output.

Notes

The newly released TOCOL function could also be used in this challenge, but was unavailable at the time of writing.

The best way to explore and understand these formulas is to use the Onion Framework to create them for yourself, starting with the innermost function.

Find previous Formula Challenges archived here.

How To Use The TRIM Function In Google Sheets To Clean Your Data

The TRIM function in Google Sheets removes unwanted spaces around text.

Trim Function Example In Google Sheets

TRIM removes the leading, trailing, and repeated spaces in the text values in column A.

The formula is:

=TRIM(A2)

🔗 Get this example and others in the template at the bottom of this article.

Continue reading How To Use The TRIM Function In Google Sheets To Clean Your Data