Paul Mireault

Paul Mireault

Seasoned Excel developers were invited to participate in a challenge to implement a spreadsheet with multi-dimensional variables. We analyzed their spreadsheet to see the different implement strategies employed. We identified two strategies: most participants used a projection of three or four-dimensional variables on the two-dimensional plane used by Excel. A few participants used a database approach where the multi-dimensional variables are presented in the form of a dataset table with the appropriate primary key. This approach leads to simpler formulas.

Paul Mireault

In Part 1, we showed how to develop a conceptual model of a problem involving variables of multiple dimensions, like Products, Regions, Sectors and Months. The conceptual model is presented as a Formula Diagram, giving a global view of the interaction between all the variables, and a Formula List, giving a precise view of the interaction between the variables. In this paper, we present precise steps to implement a multi-dimensional problem in a way that will produce a spreadsheet that is easy to maintain

Paul Mireault

The WHERE and ORDER BY clauses of the SQL SELECT statement select a subset of rows in the result of a database query and present the result in the specified order. In a spreadsheet program like Microsoft Excel, one could use the filter and sort buttons, or use its Query or its Pivot Table tools to achieve a similar effect. The disadvantage of using those tools is that they don't react automatically to changes in the calculated values of the spreadsheet. In this paper, we develop spreadsheet formulas that implement SQL's WHERE and ORDER BY clauses.

Paul Mireault

Dimensions are an integral part of many models we use every day. Without thinking about it, we frequently use the time dimension: many financial and accounting spreadsheets have columns representing months or years. Representing a second dimension is often done by repeating blocs of formulas in a worksheet or creating multiple worksheets with the same structure.

Paul Mireault

The SSMI methodology was developed using concepts from Computer Science, Software Engineering and Information Systems and has been taught to undergraduate and MBA students and in Executive training seminars. In this paper, we describe the major characteristics of the spreadsheets developed using the methodology and show how they contribute to reduce many error causing factors.

Paul Mireault

Spreadsheets often have variables and formulas that are similar, differing only by the fact that they refer to different instances of an entity. For example, the calculation of the sales revenues of the South and East regions are Revenues South = Price * Quantity Sold South and Revenues East = Price * Quantity Sold East. In this paper, we present a conceptual modelling approach that takes advantage of these similarities and leads the spreadsheet developer to the formula Revenues = Price * Quantity. We then present simple but strict rules to implement the spreadsheet.

Paul Mireault

Developing an error-free spreadsheet has been a problem since the beginning of end-user computing. In this paper, we present a methodology that separates the modeling from the implementation. Using proven techniques from Information Systems and Software Engineering, we present strict, but simple, rules governing the implementation from the model. The resulting spreadsheet should be easier to understand, audit and maintain.