Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets Tip 256: Secret rounding trick!
Hi Reader,
Welcome to the Google Sheets Tips newsletter #256, your Monday Tuesday morning espresso, in spreadsheet form!
(Reaching your inbox on a Tuesday this week because it was memorial day yesterday in the U.S.)
➜ News
I.
Here's a nice tutorial on summarizing website articles in Google Sheets using Chat GPT and Apps Script:
Automating Blog Summarisation
II.
Google is adding new capabilities to the timeline view chart feature in Sheets.
Read more about the new capabilities here.
III.
With the new Named functions, LET functions, and LAMBDA functions, we can now create recursive formulas (formulas that call themselves) in Google Sheets.
If you're a formula geek, you'll LOVE this latest post:
Recursion in Google Sheets With Formulas
_______
➜ Google Sheets Tip #256: A secret rounding trick!
The ROUND function is a handy function that's used to round decimal places in your numbers when you don't need that level of precision.
Did you know you can specify a negative number in the function though?
With a negative number of places, it will round your numbers to the nearest ten, hundred, thousand, etc...
For example, this formula would round a number in cell A2 to the nearest ten:
=ROUND(A2,-1)It's obvious with hindsight but something I didn't know about for years!
Here are some more examples:
Nice!
Chat GPT and Bard AI solutions
I've been playing around with the two AI engines recently (Chat GPT and Google's Bard) and wanted to see how they would handle today's tip. So I fed them this prompt:
what's a formula that will round numbers like 3876 to the nearest 100?
Chat GPT solution
To round numbers like 3876 to the nearest 100 in Google Sheets, you can use the ROUND function in combination with simple arithmetic. Here's the formula you can use:
=ROUND(A1/100, 0) * 100Google Bard solution
The formula to round numbers like 3876 to the nearest 100 is:
=ROUND(number,-2)Both work of course, but Google Bard came up with the more succinct formula in this case.
Have you tried using any AI tools in your Google Sheets workflows yet?
_______
If you enjoyed this newsletter, please forward it to a friend who might enjoy it.
Have a great week!
Cheers,
Ben
Google Sheets Tips
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #365, your Monday morning espresso, in spreadsheet form! Fall is beautiful on the Eastern seaboard of the US right now. The colors near me are peaking in an explosion of reds, oranges, purples, and yellows: ➜ News I.More powerful pivot tables now available in Connected Sheets. Connected Sheets lets you use the Sheets interface to analyze BigQuery data (huge datasets with millions of rows). Read more here >> II.Google...
Hi Reader, Welcome to the Google Sheets Tips newsletter #364, your Monday morning espresso, in spreadsheet form! Thanks to the hundreds of you who signed up for the Modern Google Sheets course last week. I'm excited to teach the workshops, which start next week. And thanks for your patience with the extra course promotion emails. We're now back to business as usual, with the once-a-week Google Sheets Tips newsletter. Lastly, Happy Halloween! It's a fun night here in our little town. Most of...
Hi Reader, Welcome to the Google Sheets Tips newsletter #363, your Monday Tuesday* morning espresso, in spreadsheet form! (* arriving in your inbox on a Tuesday, because it was Columbus Day in the US yesterday.) Almost two years ago, I launched a course called "The AI Playbook for Google Sheets", which explored how to use basic AI inside Sheets. (Fun fact: this was my last course launch!) A lot has changed since then! Today, Gemini and other AI tools can manipulate data, generate formulas,...