1-21-2016: Formulas, Calculations, More Formulas, More Calculations

I spent another day working on the same thing as yesterday: the calculator for Tuition Revenue Generated from the ELC. I made huge strides with this today.

Mrs. Spurgeon took a look at the calculator that I worked on yesterday, and she gave me a lot of suggestions and ideas to take it further. I worked today to implement her suggestions and tried to figure out how to fix the mistakes that I had made yesterday. I also incorporated more ways for the calculator to still work properly even if users mistakes, by adding more formulas.

A lot of what I have said from the past several days is difficult to understand without actually seeing it. Therefore, I’ve made up some sample data so I can actually show you parts of my calculator.

Here is a picture of the calculator’s inner workings. I used Microsoft Excel’s Trace Precedents and Trace Dependents features, which show all data coming to and from each cell on my page. Each blue line represents “travel” of data to another cell, and the arrowheads indicate where the data is going. You can see how complicated it is from the inside based on all of the arrows you see and there are still more parts of it. At the top of the screen, you’ll also see the formula for one of my cells at the bottom of the screen, D151. This is what I’m talking about when I mentioned yesterday that the formulas in the calculator are long and difficult.

Here is a picture of the calculator with the same information, but from the perspective of the user. You can see that all of the fields that user fills in (the ones that are blue and purple) lead to another place (most of them go directly inside the calculator). Everything goes back to the cell that contains the answer (not shown) that is calculated completely.

The reason why the calculator is so complicated is because it allows flexibility for the user. The blue cells that you see in the user view of the calculator have drop-down menus that allow the user to select two or more options. This is good for me, not only because it limits the answers that the user can give to the model (thereby eliminating errors in input), but also because it allows the user to choose his or her preferred method of presenting information to the model. For example, the question in cell A11 asks the user which way he or she would like to present data to the model, and the user can choose to type in values for each subsequent year (if you will the picture more closely by clicking on it, it will make sense), or the user could choose to set an increase in enrollment of a certain number of students each year (and if the user were to select this option, different text would appear, prompting the user to input the number of students to add for each year). However, everything becomes more complicated when I build in flexibility, which is why by the end of the day, I still wasn’t finished working on the calculator.

I still have five more formulas to write before I can hopefully be done with it! Another day working on the calculator tomorrow!