Browse Prior Art Database

Data Input Quality and Analytics Testing for Spreadsheets Disclosure Number: IPCOM000246018D
Publication Date: 2016-Apr-26
Document File: 5 page(s) / 57K

Publishing Venue

The Prior Art Database


Disclosed is a system for quality testing spreadsheets. The system automatically implements functions to identify whether values are within tolerances, whether items are included in a list of valid values, etc., and uses analytics to verify that spreadsheet values are viable.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 29% of the total text.

Page 01 of 5

Data Input Quality and Analytics Testing for Spreadsheets

When working with a spreadsheet, if a user makes common copy and paste mistake, a spreadsheet then provides skewed numbers. Another reason for a significant variation in numbers can be business-related, which needs to be investigated. Often, these errors and variations are not found until months after the initial entries are made, and are found to have skewed numbers for a long time.

To address these problems, users can include functions that verify that values are within tolerances, are consecutive (i.e. no numbers missed in a sequence), are included in a list of valid values, etc. These checks are possible for users who know how to write complex formulas, create macros, and perform programming; however, these spreadsheet skills are beyond the skills of many users. Other solutions to resolve spreadsheet errors often rely on manual review of the spreadsheet results by very experienced employees. Alternatively, functions can be developed if the group has the experience to do so.

The novel solution is a system that provides a set of functions and analytics (i.e. algorithms) that is built into the spreadsheet or in a separate "Spreadsheet Analytics" processor. The functions and analytics help answer questions such as:

 Why is a profit not shown for this month?

 What are the sources of the costs?

 What is causing a large variation in the numbers?

The system's functions and algorithms are built into a spreadsheet. Analytics are also built into the spreadsheet to help verify that spreadsheet values are viable (i.e. "make sense"). In addition, the system uses analytics to aid the spreadsheet user in exploration and investigation of past business performance to gain insight and drive business planning.

The set of functions and analytics for a spreadsheet program provides any combination of a set of components. One is Custom User Configured testing, which enables pre-configured quality testing of spreadsheet input information; this testing is easily customized and configured for specific spreadsheets. Another component is for analytics. The analytics component checks for common mistakes as well as complex variations in data and formulas and/or mistakes based on analytics. Customization and configurations can include functions that verify that specified values are within tolerances as set by the user, required items included in a list of valid values (provided by the user), etc.

Examples of Analytics include:

 Testing to identify whether complex formulas do not correctly align with peers. This not only checks for inconsistencies in the rows and columns of adjacent cells, but also determines whether the underlying data is viable (i.e. the result is desirable given the totality of the circumstances). Examples include combining monthly data to produce quarterly data and verifying that data copied from other


Page 02 of 5

areas of sheets is properly aligned. In addition, th...