Nine months after the introduction of Gemini AI into Google Sheets, Google has expanded its capabilities to include formula generation, enhancing the AI system’s utility beyond just text and charts.
The Gemini chatbot can now be found on the right side of Google Sheets. Users seeking guidance on data manipulation can ask Gemini for suggested formulas, which include detailed step-by-step instructions on how to implement them. In instances where errors occur, the AI offers advice on correcting the issues. Gemini also provides explanations if multiple formulas are available for a specific task.
Many users struggle with identifying the correct functions needed to perform specific tasks, such as distinguishing between COUNTIF, COUNTIFS, or COUNTUNIQUE. I decided to test Gemini’s formula capabilities using my wedding guest spreadsheet, a complicated mix of names, email addresses, and related checkboxes used by my partner and me to manage our ever-expanding guest list.
Even though my wedding was held in May, the intricacies of the spreadsheet still haunt me.
I anticipated the calculations required for my guest list would be straightforward, but whether Gemini’s feature could navigate the disorganized data would determine its practical value in my daily activities.
I initiated by selecting a suggested formula from Gemini aimed at counting the guests who confirmed their attendance. The formula utilized the COUNTIF function on the column labeled “RSVP.” Typically, I would have needed multiple searches online to confirm the best function for counting specific criteria in a list. However, with a single click, I could directly apply the suggested formula to my spreadsheet.
While I fondly reminisce about my wedding day surrounded by loved ones, Gemini’s formula revealed no guest had responded positively to their invitations. I soon realized I had mistakenly used an incorrect column for tracking RSVPs, despite its header indicating otherwise. Actual responses were recorded on our wedding planning site — a detail I had overlooked until Gemini pointed it out.
Undeterred, I attempted a more complex request: calculating the total distance traveled by guests to attend the wedding. Given the geographical differences between my partner’s and my families and friends, I anticipated the total would be substantial.
Gemini, however, expressed limitations, stating, “I’m still learning and can’t directly calculate distances for you in the spreadsheet.”
Nevertheless, it provided alternative methods to tackle my request manually, either through a custom script or by calculating straight-line distances using a predefined formula.
The custom script method would involve delving into APIs, specifically referencing Google Maps for accurate travel distances, which isn’t straightforward within Google Sheets. Meanwhile, calculating the straight-line distance would necessitate the Haversine formula, which requires latitude and longitude data.
To explore this option, I sought Gemini’s assistance for the Haversine formula, but the initial response was in mathematical notation, which wasn’t useful. After refining my query for a Sheets formula, I received the relevant calculation:
=6371 * ACOS(COS(RADIANS(90 – A2)) * COS(RADIANS(90 – E2)) + SIN(RADIANS(90 – A2)) * SIN(RADIANS(90 – E2)) * COS(RADIANS(B2 – F2)))
Gemini also included an explanation about the constant “6371” representing Earth’s radius in kilometers, and for miles, the value of 3959 could be used. However, when applying the formula, Gemini encountered an error due to the positioning of my latitude and longitude data.
After adjusting the input to reflect the correct data columns and switching to miles, the formula yielded a distance that matched another calculation I verified independently.
Ultimately, I was reluctant to find coordinates for each guest’s address, which diminished my initial enthusiasm for this task. Nonetheless, I left feeling assured that I could complete it with Gemini’s guidance.
