Separating Logic from Data
- Details
- Category: General
- Published on Thursday, 09 February 2012 17:26
- Written by Dave Green
A problem I have come across many times over the years is where people use the same spread sheet (sheet not book) to store data and the calculations pertaining to this data.
Superficially this isn’t a problem as long as you are careful when updating the data and the formulas in the sheet. However, after time errors creep in due to data format, data layout and output requirement changes. And this is where the problems occur.
Let’s take a look at a few examples of what can go wrong:
Data Format: If data isn’t manually entered into a spread sheet, then it is often copy pasted from another location. This could be another spread sheet which is generated from a Data Base dump, or from a CSV file. Sometimes the data may have to be copy/pasted from a word or PDF document. All of these sources could cause errors in your spread sheet - for example the data you paste may be pasted as text and not numbers.
On the above screenshot it is obvious that there is some text in this column of numbers (Hint: it’s cell A5) but what is not obvious is that cell A6 also includes text (the value 19 is entered as text and the cell is right aligned so it appears to be a number). You probably noticed this as the column of numbers is short and you added them up in your head, and came to 178 and not 159. Now imagine a set of data containing hundreds or thousands of rows.
To be fair, keeping your data on a separate sheet to the functions does not make the above go away - however what it does do is allow you to play with the data to wheedle out the 'bad data' without fear of damaging functions contained on the same sheet. (more on this to follow in a seperate article).
Data Layout: Ever had a spread sheet nicely set up in which you regularly paste data from a database or some other document and after several months of spread sheet nirvana, the format of the data changes? I am not talking about numbers as text here, but additional columns, and columns in different order? Over the years I have had this happen many times, and regardless of how nicely I ask to revert to the old format, the response is always "that's how the data is, you will have to work around it'.
Now, as much as I would like to tell the people sending the data to get their act together, and stop making my life
difficult, I have come to the realisation I cannot expect others to be that helpful all the time (and sometimes these things are even beyond their control, so it’s best to give them the benefit of the doubt) and therefore I have to be prepared for the inevitability that things will change, and totally screw up my awesome spread sheets. Therefore it is down to me to ensure my spread sheets are flexible enough to cope with these changes and are easy to update and modify as needs arise. This is one of the major pluses of keeping data and logic separate where possible.
Output requirements: How many times does your boss look at the report you have lovingly spent hours of your life crafting to meet the very brief given to you, to have him say, “Hmmm, not bad. Can you also include XYZ and somehow hide the negative looking stuff?”
I know. Too many times to keep count.
Keeping your data separate from the logic is where this pays off again. Just change the sheet(s) with the calculations and ‘et viola’. No mucking around with the raw data upsetting some calculations just to add some new ones.
And now the really cool part…
Ok – It’s not that cool, it’s a spread sheet! However, to make things even better we get to use the most exciting buzzword in the world of excel since the invention of Pivot Tables, and marvel at the fact that despite all the mystique and hype given by people selling excel add ins etc. you and I have been using ‘Dashboards’ for years… Oh yeah! Read the last bit again and feel the warm fuzzy feeling as you realise you are ahead of the curve.
All a dashboard is in essence is another layer in a properly presented workbook – it’s the presentation of a summary with graphs etc. in one sheet at the front of the workbook for your boss to look at.
So to recap – the layers are basically:
- Data.
- Logic and calculations.
- Presentation. (Dashboard!)
Keeping things separate keeps things tidy and logical.
That’s enough for now – but I will cover some of the above in more detail in following posts.