Unraveling the Frustration: Using Gemini with Google Sheets

As a regular user of Google Sheets for work, I often rely on tools and features that enhance my productivity. Recently, I discovered that Gemini has been integrated into Sheets, sparking my excitement for potential improvements in efficiency. However, my experience with this feature left me perplexed and, frankly, a bit frustrated.

The Dilemma: A Formula for Excel, Not Sheets

In my attempt to leverage Gemini’s capabilities, I requested a specific formula through the prompt: “For cell Y1219, I’d like to find the average of the 10 smallest numbers in D1222:D1281.” To my surprise, Gemini replied with the formula =AVERAGE(SMALL(D1222:D1281, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10})), resulting in an average of 66.12. Unfortunately, this seemed far too low, as it was the smallest number in my dataset.

To verify, I manually sorted the values in that range and calculated the average of the smallest ten, which came out to 75.616—a significant difference. After some iterations and restating my request, I received the exact same formula again, consistently returning the wrong result. It soon became clear that Gemini was not suited to providing context-specific assistance for Google Sheets.

Seeking Guidance Through Troubleshooting

Determined to find a solution, I revisited the standalone Gemini tool in a separate tab. Yet, when I laid out my issue again, the response remained unchanged, directing me to troubleshoot aspects like number formatting. Everything was indeed formatted correctly, yet I was still stuck with the same inaccurate result.

Finally, after several back-and-forth exchanges, the underlying issue was revealed: the disparity in how Google Sheets and Excel handle array formulas. It became evident that I needed a different approach entirely. Gemini ultimately provided me with the correct formula: =AVERAGE(ARRAYFORMULA(SMALL(D1222:D1281, SEQUENCE(10, 1)))), which successfully yielded the average I was seeking.

A Call for Improvement

While I am grateful to have found a solution, this experience raises questions about the integration of Gemini with Google Sheets. It seems counterintuitive for a tool designed to boost productivity to generate formulas that are incompatible with the software in which it operates. My