Gemini Fixes Your Broken Google Sheets Formulas
▼ Summary
– Gemini AI in Google Sheets now helps users create and troubleshoot formulas, providing step-by-step explanations and correction guides when errors occur.
– The author tested the feature on a complex wedding guest spreadsheet, starting with a simple COUNTIF formula to tally RSVPs, which revealed a data tracking error.
– For a more complex request to calculate total guest travel miles, Gemini could not perform the calculation directly but offered two manual methods.
– One method involved using the Google Maps API, while the simpler alternative used the Haversine formula for straight-line distance calculations after providing latitude and longitude data.
– The author successfully implemented the Haversine formula with Gemini’s guidance, confirming its accuracy, but found the process too tedious to complete for all guests.
Google has significantly upgraded its Gemini AI assistant within Google Sheets, moving beyond simple text generation to tackle one of the most common user pain points: broken and complex formulas. This new capability aims to transform how users interact with their data, offering not just answers but detailed explanations and troubleshooting guidance directly within the spreadsheet interface.
The AI chatbot, which appears in a sidebar, now provides suggested formulas along with step-by-step instructions for implementation. When a formula fails, Gemini analyzes the error and offers a clear guide for correction. If several different formulas could achieve the same result, the system explains the various options, helping users choose the most appropriate one for their specific task. This is particularly useful for navigating the often-confusing world of functions like COUNTIF, COUNTIFS, and COUNTUNIQUE.
To put this feature to a real-world test, the author applied it to a notoriously chaotic wedding planning spreadsheet. The first experiment was straightforward: asking Gemini to create a formula counting guests who responded ‘Yes’. The AI instantly generated a correct COUNTIF formula, saving the typical time spent on multiple Google searches. However, the result revealed an unexpected data integrity issue, the formula showed zero positive RSVPs. This highlighted that the intended RSVP column was empty; acceptances were actually being tracked on a separate wedding website the author had completely forgotten about. Gemini’s response, while technically accurate, uncovered a critical flaw in the spreadsheet’s organization.
A more ambitious query followed: calculating the total miles all guests traveled to attend the wedding. Gemini’s initial response was a polite refusal, stating it couldn’t directly calculate distances. Instead of leaving the user stranded, it offered two potential pathways. The first involved using the Google Maps API, a powerful but technically demanding solution requiring custom scripts. The second, simpler alternative was to calculate straight-line distances using the Haversine formula, which requires latitude and longitude coordinates.
Upon request, Gemini provided the complex Haversine formula in a format ready for Google Sheets. The initial output used mathematical notation, but a follow-up query yielded a working Sheets formula. The AI also explained key components, such as the number 6371 representing the Earth’s radius in kilometers, and noted that 3959 should be used for miles. After substituting the correct cell references and adjusting for miles, the formula produced a distance that was verified by an external calculator website. While the prospect of manually finding coordinates for every guest address ultimately made the project impractical, the experience demonstrated that with Gemini’s guidance, even highly complex calculations become achievable. The tool provides the necessary building blocks, empowering users to solve problems they might otherwise abandon.
(Source: The Verge)
