Hi Reader,

Welcome to the Google Sheets Tips newsletter #301, your Monday morning espresso, in spreadsheet form.

Earlier this year I sat down to record a new course but progress stalled before I got far. I've been creating courses for 7 years and realized that I need to freshen things up.

One change I want to make is to inject more meaning into my work.

I'm deeply interested in our natural world and protecting it for future generations. I want to use the skills I have and apply them towards that goal.

Perhaps you can help.

Do you work in any of these industries, as a scientist, educator or other role?

• ecology and environmental science,
• conservation,
• climate change advocacy,
• natural history,
• or similar...

If so, I'd love to hear from you!

On a similar vein, I'm on the other side of the classroom this week. I'll be a student in Jer Thorp's Binoculars to Binomials online cohort course. Billed as a "nature + data" course, I'm excited!

A big thank you to this week's sponsors who help keep this newsletter free to read:

➜ News

I.
​What's new in the AppSheet world?

On this week's Totally Unscripted show, the engineering lead of AppSheet, Mike Procopio, joins the TU team to showcase developments in the AppSheet world.

​Watch it here on Wednesday at 3pm Eastern time >>​

_______

➜ Google Sheets Tip #301: Build Data Sets Fast with MAKEARRAY

A few weeks ago, for tip #298, I needed a dataset of fake test scores. In this tip, we're going to see how to use the MAKEARRAY function to do this.

The MAKEARRAY function is one of the newer LAMBDA functions in Google Sheets.

LAMBDA functions are very powerful tools but they are a little more complex than regular functions. Mostly, they involve a range of data and then an inner function (called the LAMBDA function) to apply to each element of the range.

The MAKEARRAY function in Google Sheets generates an array of a specified size. Each value in the array is generated by the inner LAMBDA function.

The syntax for MAKEARRAY is:

=MAKEARRAY(rows, columns, lambda)

We tell the formula how many rows and columns we want, and then include the formula we want to apply to each value.

This inner formula has access to the row and column coordinates. I.e. for the first value in the range, the row value is 1 and the column value is 1.

Let's see an example:

Simple MAKEARRAY

Enter this formula in a Sheet:

=MAKEARRAY(3,2,LAMBDA(r,c,r+c))

This generates an array with 3 rows and 2 columns:

The inner formula is:

LAMBDA(r,c,r+c)

Here, the r represents the value of the row (e.g. r = 1 for the first row, r = 2 for the second row, etc.)

And c holds the value of the column.

Then the expression r + c determines how to calculate the output. Here, we simply add together r and c.

So for the first position of our array, r = 1 and c = 1 so

r + c = 1 + 1 = 2

which we can see in the image above.

Then the formula moves to the second position and performs the operation again. This time:

r + c = 2 + 1 = 3

Generate data with MAKEARRAY

We can have whatever we like as the formula inside the LAMBDA.

So let's use the RANDBETWEEN function to generate those fake test scores.

The formula

RANDBETWEEN(40,95)

generates a random number between 40 and 95.

We use that inside the LAMBDA and simply ignore the row and column values in this case.

Enter this formula in cell B2:

=MAKEARRAY(10,4, LAMBDA(i,j, RANDBETWEEN(40,95)))

This formula generates an array with 10 rows and 4 columns.

Inside the LAMBDA, i holds the row coordinate and j holds the column coordinate. But I don't use either, which is why i and j don't show up again.

The inner formula is the RANDBETWEEN(40,95) that generates a random number for each position in the array.

Nice!

The LAMBDA functions are more challenging than regular functions but they are worth learning because they are so powerful.

💡 More on MAKEARRAY

• Learn more about LAMBDA functions in my free Lambda Functions 10-Day Challenge course
  ​
• See tip #250 for another MAKEARRAY example

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. A birthday surprise for this conductor.