Spreadsheet Operations Research Articles

Spreadsheets on Interactive Surfaces: Breaking through the Grid with the Pen

Spreadsheet programs for interactive surfaces have limited manipulations capabilities and are often frustrating to use. One key reason is that the spreadsheet grid creates a layer that intercepts most user input events, making it difficult to reach the cell values that lie underneath. We conduct an analysis of commercial spreadsheet programs and an elicitation study to understand what users can do and what they would like to do with spreadsheets on interactive surfaces. Informed by these, we design interaction techniques that leverage the precision of the pen to mitigate friction between the different layers. These enable more operations by direct manipulation on and through the grid, targeting not only cells and groups of cells, but values and substrings within and across cells as well. We prototype these interaction techniques and conduct a qualitative study with information workers who perform a variety of spreadsheet operations on their own data.

A simpler method for understanding emergency shelter access patterns

ABSTRACT The Simplified Access Metric (SAM) is a new approach for characterizing patterns of homelessness. The goal of SAM is to provide emergency shelter operators and housing staff with an intuitive way to understand a person or group’s pattern of homelessness and/or shelter access that can be implemented by non-technical staff using spreadsheet operations. Client data from a large North American shelter will be used to demonstrate that SAM produces similar results to traditional transitional, episodic and chronic client cluster analysis. Since SAM requires less data than cluster analysis, it is also able to generate a timeline of homelessness patterns that can be updated in real time. Using nine years of shelter data, a shelter access timeline is presented that includes the introduction of Housing First programming and the COVID-19 lockdown. Finally, SAM allows shelter staff to move beyond assigning transitional, episodic and chronic labels and instead use the “soft” output of SAM directly to better understand a person’s experience of homelessness.

A simple approach for reversed phase column comparisons via the Tanaka test

The Tanaka test is a classical characterization scheme designed in 1989 for highlighting the main reversed phase attributes of C18 columns: hydrophobicity, shape/steric selectivity, hydrogen bonding, ion exchange (pH < 7 and pH > 7 environments). In this short communication, we present it in a simplified manner that requires basic HPLC and spreadsheet operations; encouraging analysts of all levels of expertise to begin capturing an in-house library of column comparison knowledge based on their laboratory’s unique set of phases and previously developed separations. This approach does not compete, nor replace existing techniques such as quantitative structure-retention relationship (QSSR) models to aid column selection. It is simply an alternative or additional aid to support column selection decisions that is easily accessible and implementable.

Novel Spreadsheet Direct Method for Optimal Control Problems

We devise a simple yet highly effective technique for solving general optimal control problems in Excel spreadsheets. The technique exploits Excel’s native nonlinear programming (NLP) Solver Command, in conjunction with two calculus worksheet functions, namely, an initial value problem solver and a discrete data integrator, in a direct solution paradigm adapted to the spreadsheet. The technique is tested on several highly nonlinear constrained multivariable control problems with remarkable results in terms of reliability, consistency with pseudo-spectral reported answers, and computing times in the order of seconds. The technique requires no more than defining a few analogous formulas to the problem mathematical equations using basic spreadsheet operations, and no programming skills are needed. It introduces an alternative, simpler tool for solving optimal control problems in social and natural science disciplines.

Theorizing spatial dynamics of metropolitan regions: A preliminary study in Java and Madura Islands, Indonesia

This study is aimed at exploring the spatial dynamics of metropolitan regions in Java and Madura Islands, which are commonly known as the most developed areas in Indonesia. The analyses have used GIS-based and tabular data methods applying spreadsheet operations detailed at sub-district (kecamatan) level. An urban sub-district is defined as that with a built-up ratio of more than 22.5% and a population density of more than 1250 people/km2. Delineation of the built-up areas has used an unsupervised classification of 741-bands of Landsat TM7 satellite imageries for the years of 1990 and 2011. The potential sprawl is based on the comparison between the rate of population growth and that of built-up areas. The results show that generally, built-up areas significantly grow on the fringe zones of Java big cities. In metropolitan regions, an indication of non-compact growth occurs, albeit agricultural activities are still dominant there. Sprawl is found to be more in rural and outer than suburban and inner peripheral areas. In this regard, local governments should pay more attention to this issue because it can endanger the food security and environmental sustainability.

Are you having fun teaching Excel? Using an electronic classroom management system

In modern education, computer applications are capable of enhancing the quality of teaching and learning. Instructors teaching spreadsheet applications such as Excel are faced with a variety of assessment and teaching methods to assign and grade homework projects that guide students in mastering the essentials of spreadsheet operations. Several grading software packages are available in the market to increase the efficiency and effectiveness of teaching an Excel course. This study discusses a system designed to create and grading Excel assignments. The system's advantages and drawbacks are discussed and compared to the packages available in the market.

Flatbrain spreadsheets: Mindtool outside the box?

AbstractManaging the pedagogical aspects of the ‘computational turn’ that is occurring within the Humanities in general and the disciplines associated with cognitive science and neuroscience in particular, first implies facing the challenge of introducing students to computation. This paper presents what has proven to be an efficient approach to bringing undergraduate Humanities students to reach insight into the nature of computation and its bearing on reflecting upon the mind in general, and the brain in particular. It is set within the context of a course on the topic of sensory perception featuring a laboratory component aimed at guiding students to develop neuronal networking skills. In this course, students are asked to design, test and discuss the neurophysiological, psychological and philosophical implications of the neuronal blueprints of a virtual creature’s brain which they are challenged to ‘wire’ themselves in such a way as to allow it to ‘see the world’ within which they choose to place it. The insight on which we are reporting here is simply that a basic competence in using a spreadsheet application is all that is required to allow implementing and testing of virtual brains made of basic formal neurones, bringing the miracle of computer simulation within the reach of even the most computer‐shy undergraduates. Once introduced to basic neuronal networks (two 90‐minute laboratory sessions), two laboratory sessions are sufficient to bring groups of up to some 50 undergraduates to manipulate the basic spreadsheet operations successfully and understand how virtual brains consisting of basic formal neurones can be implemented in terms of these basic spreadsheet operations. It is the ‘flattening’ to which the virtual (formal neuronal) brains are thus subjected, as they are turned into spreadsheets that led to coining the concept of a ‘flatbrain spreadsheet’. The students are then challenged to develop and implement their very own virtual creature’s flatbrain spreadsheets, and gently tutored into noticing the key problems out of which arise the great debates in cognitive science about such issues as consciousness, qualia, categorisation, induction, computational explanation and the like. Empirical evidence gathered over the course of the last 6 years strongly suggests that the construction of flatbrain spreadsheets by students does make a difference in the classroom.

MedCalc Software for Statistics in Medicine. MedCalc Software, Broekstraat 52, 9030 Mariakerke, Belgium, $399.00.

MedCalc for Windows is a computer program for statistical applications in laboratory medicine. The package consists of a 161-page Program Manual and Software for Windows 3.1 or Windows 95. The Manual consists of a Table of Contents followed by a section on each of the windows in the main menu: File, Edit, Data, Statistics, Graphs, Tests, Sampling, Window, and Help windows. This is followed by sections on MedCalc Spreadsheet operations and functions, samples cases and four Appendices on the definition of control keys, functions, notations and symbols, and a brief set of statistical tables. The Manual concludes with literature references and a subject index. Overall, the manual read very well. It shows samples of the dialog panels that guide the user in the operation of this software package. The manual also includes definitions and basic discussion on the meaning and purpose of a given calculation or …

2 Barrier–1 site pore: an interactive spreadsheet model for two permeating ions

An interactive ion channel permeation tutorial was developed using a Microsoft® Excel v5.0 spreadsheet to describe a two barrier, one site channel model by means of Eyring rate theory (ERT). The spreadsheet is inherently interactive so that the user receives immediate feedback about how changes in the energy barrier profile or ion concentrations affect current–voltage relations and single channel conductance. The spreadsheet model is easy to use, allows direct access to intermediate calculated values and all equations, contains graphical displays of parameters and lends itself to customization by a user having only a basic knowledge of spreadsheet operations.

StreamPlan: a support system for water quality management on a river basin scale

StreamPlan was developed as an integrated, easy-to-use software system for analysing alternative water quality management policies on a river basin level. These include uniform emission reduction and effluent standard based strategies, ambient water quality criteria and least-cost strategies, total emission reduction under minimized costs, mixed strategies, local and regional policies, and strategies with economic instruments. A distinctive feature of StreamPlan is the integration of a detailed model of wastewater generation on the municipal level with water quality modelling and policy analysis tools on a river basin scale. StreamPlan allows the modelling of cost and abatement efficiency of different wastewater treatment plant upgrading schemes based on projected wasteloads and a description of existing facilities. StreamPlan is composed of six spreadsheet workbooks and an external linear programming package. The power and familiarity of spreadsheet operations enhance ease of use. The spreadsheet's own programming language seamlessly integrates databases and models in workbooks and worksheets into a complete system. Moreover, the total development time for the system was just over one person year.

System Modeling Through Parametric Spreadsheet Analyses

AbstractOperational and cost effectiveness analyses are essential elements in the decision making process for both government and civil programs. Programs, now more than ever, require integrated system level models to perform early assessments of system tradeoffs between operational capability, performance, cost and schedule. At Rockwell Space Systems Division, an approach has been developed to achieve easy integration of various quantitative products of integrated product teams into a “system model”. This approach, at the present, is based on use of spreadsheets. We have taken advantage of the availability of PCs and Macs and familiarity of most engineers with spreadsheet operations. A demonstration of this approach is presented in the context of a satellite development program.

Book review: 1-2-3 Macro Library (3rd Edition) by David Paul Ewing (QUE Corporation 1990)

This hefty 734-page book isolates a major feature of Lotus 1-2-3 which is the creation, development and use of MACROS as a tool to facilitate one's work with spreadsheets. Macros are especially useful when one realizes the number of repetitive keystrokes that can be reduced in a given work session if one only knew how and if the software package were capable of storing your keystrokes. Anymore now, most productivity packages whether they be wordprocessors, spreadsheets or databases come equipped with these keystroke-saving features called MACROS. Thus, learning how to make use of MACROS can certainly save the user a lot of keystrokes. However, one should already be proficient in the basics of spreadsheet operations before tackling this book. It assumes the reader already is well-versed in the various features of 1-2-3 and therefore does not even introduce how to create a macro in its first chapter.

Spreadsheet calculations of probabilities from the F, t, χ 2 , and normal distribution

By computing probabilities from the normalization of the F distribution (instead of by numerical integration methods), statistical capabilities in spreadsheet operations can be greatly expanded and enhanced.