Avoiding,finding and fixing spreadsheet errors – A survey of automated approaches for spreadsheet QA

Spreadsheet programs can be found everywhere in organizations and they are used for a variety of purposes, including financial calculations, planning, data aggregation and decision making tasks. A number of research surveys have however shown that such programs are particularly prone to errors. Some reasons for the error-proneness of spreadsheets are that spreadsheets are developed by end users and that standard software quality assurance processes are mostly not applied. Correspondingly, during the last two decades, researchers have proposed a number of techniques and automated tools aimed at supporting the end user in the development of error-free spreadsheets. In this paper, we provide a review of the research literature and develop a classification of automated spreadsheet quality assurance (QA) approaches, which range from spreadsheet visualization, static analysis and quality reports, over testing and support to model-based spreadsheet development. Based on this review, we outline possible opportunities for future work in the area of automated spreadsheet QA.     

基于流水线装配生产率问题的Spreadsheet仿真模型

在流水线生产中,由于两工序间存储空间有限,前序工人与后续工人之间相互影响.为了研究流水线中存储空间对生产率的影响,提高流水线工人生产率,本文利用Spreadsheet建立仿真模型,通过仿真模拟,研究分析流水线生产中两工序之间没有存储空间时及增加存储空间后对生产效率的影响.为解决流水线装配问题提供一种新的方法.     

Gneiss: spreadsheet programming using structured web service data

Web services offer a more reliable and efficient way to access online data than scraping web pages. However, interacting with web services to retrieve data often requires people to write a lot of code. Moreover, many web services return data in complex hierarchical structures that make it difficult for people to perform any further data manipulation. We developed Gneiss, a tool that extends the familiar spreadsheet metaphor to support using structured web service data. Gneiss lets users retrieve or stream arbitrary JSON data returned from web services to a spreadsheet using interaction techniques without writing any code. It introduces a novel visualization that represents hierarchies in data using nested spreadsheet cells and allows users to easily reshape and regroup the extracted structured data. Data flow is two-way between the spreadsheet and the web services, enabling people to easily make a new web service call and retrieve new data by modifying spreadsheet cells. We report results form a user study that showed that Gneiss helped spreadsheet users use and analyze structured data more efficiently than Excel and even outperform professional programmers writing code. We further use a set of examples to demonstrate our tool's ability to create reusable data extraction and manipulation programs that work with complex web service data.     

Hitting the wall: errors in developing and code inspecting a `simple' spreadsheet model

Field audits and experiments have found substantial error rates when students and professionals have built spreadsheet models. In this study, 102 undergraduate MIS majors and 50 MBA students developed a model from a word problem that was relatively simple and free of domain knowledge. Even so, 35% of their 152 models were incorrect. There was no significant difference in errors per model between undergraduates and MBAs. Even among the 17 MBAs with 250 h or more of experience, 24% of the models contained errors. The cell error rate (CER)—the percentage of cells with errors—was 2.0%. When 23 undergraduates attempted to audit their models through code inspection, only three with incorrect spreadsheets (15%) produced clean spreadsheets when they finished the audit.     

Preliminary identification of potential markets for off-grid electrification: tool development and a case study of Ghana

With more than one billion people lacking access to electricity in the world, ensuring universal access to electricity by 2030 remains a major challenge which cannot be left to the government initiatives alone. Access to local information and identification of potential areas for investment can be a challenge for investors. This paper provides a tool for preliminary assessment of potential markets for off-grid electrification in developing countries and applies this to Ghana to demonstrate its applicability. A multi-criteria approach is used to rank the districts according to the overall potential and the best markets and least favourable areas for investment are identified. The tool offers flexibility to include new inputs to the analysis and the factor weights can be adjusted as appropriate. The case study shows that the tool can effectively identify potential areas from a list of candidates and offers support to analysts.     

eGames: Is imagination the forgotten ingredient?

Commercial eGames strive to create seamless transitions between realistic virtual worlds. This condenses the experience and can create more intense engagement. However, there is no evidence that this is essential in eGames used for educational applications. Educators seek nearly opposite attributes. They need materials that can be disaggregated into units small enough to fit within timetables and that can be edited and customised to fit curricula without resort to programming. This paper proposes the alternative of a meta-game – a loose string of game and puzzle tasks in the tradition of heroes’ journeys, tournaments, scavenger hunts, and road rallies. Imagination and symbolism provide the links between otherwise unrelated clues, puzzles, and tasks. Spreadsheets serve as the ideal hub for meta-games. Current spreadsheets include form controls such as scroll bars and buttons, share the same multimedia applications as other office software, and can even create animation. The spreadsheet has the versatility to fill the niche of the cardboard box as a plaything. Research is reviewed that supports a role for eGames with less overt reward and more left to the imagination.     

Gender pluralism in problem-solving software

Although there has been significant research into gender regarding educational and workplace practices, there has been little awareness of gender differences as they pertain to software tools, such as spreadsheet applications, that try to support end users in problem-solving tasks. Although such software tools are intended to be gender agnostic, we believe that closer examination of this premise is warranted. Therefore, in this paper, we report an end-to-end investigation into gender differences with spreadsheet software. Our results showed gender differences in feature usage, feature-related confidence, and tinkering (playful exploration) with features. Then, drawing implications from these results, we designed and implemented features for our spreadsheet prototype that took the gender differences into account. The results of an evaluation on this prototype showed improvements for both males and females, and also decreased gender differences in some outcome measures, such as confidence. These results are encouraging, but also open new questions for investigation. We also discuss how our results compare to generalization studies performed with a variety of other software platforms and populations.     

Dynamic fault trees resolution: A conscious trade-off between analytical and simulative approaches

Safety assessment in industrial plants with ‘major hazards’ requires a rigorous combination of both qualitative and quantitative techniques of RAMS. Quantitative assessment can be executed by static or dynamic tools of dependability but, while the former are not sufficient to model exhaustively time-dependent activities, the latter are still too complex to be used with success by the operators of the industrial field.In this paper we present a review of the procedures that can be used to solve quite general dynamic fault trees (DFT) that present a combination of the following characteristics: time dependencies, repeated events and generalized probability failure.Theoretical foundations of the DFT theory are discussed and the limits of the most known DFT tools are presented. Introducing the concept of weak and strong hierarchy, the well-known modular approach is adapted to study a more generic class of DFT. In order to quantify the approximations introduced, an ad-hoc simulative environment is used as benchmark.In the end, a DFT of an accidental scenario is analyzed with both analytical and simulative approaches. Final results are in good agreement and prove how it is possible to implement a suitable Monte Carlo simulation with the features of a spreadsheet environment, able to overcome the limits of the analytical tools, thus encouraging further researches along this direction.     

用电子表格软件建立动态仿真模型

本文描术了用于船舶操纵的动态仿真模型的电子表格开发环境，文中用自引用公式对动态方程进行了编程，该技术不需要电子表格的宏指令。文中还讨论了电子表格用于动态仿真建模的利弊。