Creating Effective Spreadsheets

Challenge Blog

Multi-Dimensional Spreadsheet Challenge Blog

The Challenge is now closed.

There were 109 kit requests and 17 submissions. I will analyse those submissions and post some results here. I will submit a paper at EuSpRIG19 and I will keep all 109 participants appraised of the results.

Even though Aussies requested 39 kits, they have only 5 submissions (13%). The Brits requested 11 kits and have 5 submissions also (45%) and the Americans requested 7 kits and have 3 submissions (17%).

Thank you to all those who tried the challenge, wether they submitted a spreadsheet or not. I will keep you informed of the results. (But don’t expect results before July 2019, when I hope to present my analysis at EuSpRIG19.)

I’ll try to keep these statistics current. I used the country code of the email address of the participant, or information supplied in the email signature or personal knowledge. Those who have requested a Kit from a generic address can inform me of their country by sending me another email. Thanks.

The number of Kit requests has passed the 100 mark!

I’m impressed: Australia has a very dynamic Excel community for its size, with 39 requests. (Is there something in the water! Or is it because their Summer is coming?) The UK has 11, the US has 7, Ireland and Nigeria have 3, Greece, the Netherlands, and Poland have 2 and the others have 1 each.

There is still a big number of Unknowns.

There are now 17 submission.

Number of Kit Requests by Country

Cumulative Number of Kit Requests and Submissions

Q: On your inputs in the data worksheet, are we able to reformat? I have a way I like thing ... It won’t change the values themselves.

A: Yes you can reformat the data sheet to suit your needs. But you have to leave the Interface as it is.

2018-11-14 Reformat the inputs

2018-11-08 Macros and VBA

A few participants ask if they can use macros or VBA in their solution. YES, you can. But if you use old technology, like Excel 4.0, I may have to communicate directly with you (Skype or FaceTime) to get further explanations.

2018-11-04: No Rounding

A Challenge participant wonders why my formulas don’t round the results when they calculate integers, like unit sales.

There is always a debate about that. Here, we are dealing with forecasted estimates, and you can consider them as “expected averages”. Rounding an average is incorrect, even when the value doesn’t seem to make sense: the “average” family has 2,3 children. On the other hand, if you have a model that evaluates the number of electrical plants you need to supply a given population and you get 2,3, then you should round it up to 3.

In this case, I choose not to round the results. If you prefer to round them, then you will not be able to compare your results with mine. But I would not consider that your model is wrong: it’s just a developer’s choice.

2018-11-04: Error in one formula

In my 36-year university career, it happened a few times: I write an exam question and verify it, and verify it, and yet again… Then, during the exam, a student notices an error and tells the proctor who then calls me. By the time I correct it and make an announcement in the exam room, some students have finished the question without noticing it or have struggled because their result did not match the example. As you might expect, I always felt bad when that happened and I would always adapt my grading in favour of the students (depending on the gravity of the error.)

Well, one challenge participant noticed it early, and I hope the others were not inconvenienced by it. It has nothing to do with the spreadsheet structure: formula 9 (the one that calculates the expected demand from the price) in the Formula List does not correspond to the description in the problem statement. The one in the problem statement is the correct one and it is the one I used to produce the two dashboard examples in the main Challenge page. The Challenge Kit has been revised and all the participants who received the erroneous have been sent the revised Formula List. The new Formula list is named Acme TechnoWidget Company - Formula List V3.pdf.

2018-11-01 : Announcement

The Multi-Dimensional Spreadsheet Challenge is announced. Click on the button: