Spreadsheet Formula Suggestion via Context Vector Analysis

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Users face difficulties in using complex formulas and functions in spreadsheets due to lack of expertise and errors, despite existing auto-complete tools and in-line function definitions, which only partly reduce friction.

Innovation Solution

Implementing machine learning models, such as neural networks and random forest models, to suggest functions and operands, and using form-based editing to ease interaction with formula parameters, enhancing user experience and accuracy.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If users manually enter complex formulas and functions in spreadsheets, then they can perform calculations, but users make errors and struggle due to lack of expertise

Engineering Contradiction:

Improveformula accuracyVSAvoidformula editing difficulty

Core Design Contradiction:

ReliabilityVSEase of operation

Solution Approach 1:

The system enables formulas to suggest and complete themselves by detecting the cell context and automatically providing function suggestions and parameter completions, reducing manual effort and errors

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system provides real-time feedback to users by analyzing the cell context and suggesting appropriate functions and parameters, allowing users to correct errors before finalizing formulas

Inventive Principle:

Principle #23Feedback

2Ease of operation

If basic auto-complete tools are provided, then some user friction is reduced, but users still struggle with complex functions and lack expertise

Engineering Contradiction:

Improveformula editing easeVSAvoidfunction usage correctness

Core Design Contradiction:

Ease of operationVSReliability

Solution Approach 1:

The system acts as an intermediary between the user and complex spreadsheet functions by providing intelligent suggestions and a form-based interface that mediates the interaction, making complex functions accessible without requiring deep expertise

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The system performs preliminary analysis of the cell context before the user completes the formula, pre-populating parameter fields and suggesting appropriate functions to guide the user toward correct usage

Inventive Principle:

Principle #10Preliminary action

3Reliability

If machine learning models suggest functions and operands, then formula accuracy improves, but system complexity increases

Engineering Contradiction:

Improveformula accuracyVSAvoidsystem complexity

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

The system replaces manual mechanical analysis of cell context with machine learning models that automatically detect and interpret context, suggesting functions and parameters based on learned patterns from training data

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS11227106B2Suggested functions for formulas in spreadsheets

Publication Date: 2022.01.18 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Systems, methods, and software are disclosed herein for enhancing functions and formulas in spreadsheets. A computing apparatus detects a beginning of a formula in a cell of a spreadsheet. In response to detecting the beginning of the formula, the computing apparatus identifies one or more vector representations of a context of the cell and supplies the one or more vector representations as input to one or more machine learning models. The one or more machine learning models produce output comprising one or more suggested functions for the formula and one or more suggested operands, which the computing apparatus presents in a user interface to the spreadsheet.