Hi Reader,

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

Coming to your inbox on a Tuesday this week because many folks have a long weekend for Easter.

In the mountains near where I live, the Virginia Bluebells are in full bloom and the trails are beautiful!

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

➜ News

I.
​This is an entertaining read on what happens when you challenge Gemini (Google's AI tool) to a game of battleship in Google Sheets!

Of course, the author (Dmitry Kostyuk) had to first create the Battleships game in his Sheet. The post runs through how he constructed the game and includes his code.

​Battleship in Sheets >>​

_______

➜ Google Sheets Tip #297: Threshold Value Formulas

Suppose we have a set of values to cap at a certain threshold level. I.e. everything above the threshold value, e.g. 100, gets set to that value. We're saying that the specific value doesn't matter beyond this threshold.

Perhaps, we would approach this by writing an IF formula. We could check whether the value is above 100 and then set it to 100 if TRUE, or the actual value if FALSE, like so:

=IF(A2>100,100,A2)

But, there is a shorter way!

We can replace the whole IF function with a more succinct MIN function.

=MIN(100,A2)

MIN outputs the smallest value between 100 and the actual value. So, if the actual value is larger than 100, the result is the threshold value of 100.

Similarly, we can use a MAX function to replace IF statements when we're looking at a threshold on the low side.

For example, suppose we're not interested in small values below a threshold, e.g. 10. This time we could use this MAX function:

=MAX(A2,10)

Writing efficient formulas is good practice. Not only is it quicker, but they're often easier to understand.

_______

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

Have a great week!

Cheers,
Ben

P.S. Chances that a cow steals your shoes are slim, but never zero​