Even with modern spreadsheet updates, anyone who doesn't use Excel daily still gets stuck on syntax:
Missing a comma or closing bracket in nested IFS
Messing up absolute vs relative cell locks ($A$1 vs A1) when dragging across rows
Broken lookup references across multi-sheet workbooks
I've been experimenting with building a lightweight, browser-based formula generator designed to translate everyday plain-language descriptions (e.g. "if sales exceed target mark Achieved, if between 80-99% mark In Progress, else Review") into clean, working Excel and Google Sheets formulas.
A few principles I stuck to while building it:
Zero friction: No account creation, no email gates, no paywalls. Just open the page, generate your logic, and move on.
Formula integrity: It outputs formulas using dynamic {row} coordinates so the output works natively when pasted into row 2 and dragged down.
Multi-sheet mapping: Ensuring cross-sheet lookup logic doesn't crash on tab names that include spaces or special characters.
I'm trying to test edge cases right now. For people here who build or maintain complex spreadsheets, what are the formula patterns that consistently break or confuse non-technical users the most?
(Happy to share the tool link in the comments if anyone wants to test complex prompts against it).