The Tyranny of the Spreadsheet Cell

Quote of the Day

Never give up on a dream just because of the time it will take to accomplish it. The time will pass anyway.

— Earl Nightingale.


Figure 1: The Concept of Cell is Both a Strength and a Weakness of Spreadsheets. (Source)

Figure 1: The Concept of Cell is Both a Strength and a Weakness of Spreadsheets. (Source)

I had a conversation the other day with an engineer to whom I was expressing my frustrations with using Excel to process large data sets of complex numbers.  She also has processed large data sets with Excel and commented that she found the processing painful. I told her about how I was using Excel to work with my data sets, but my techniques all seemed contrived and overly complex. While you can use Excel to work with these data sets, it is a bit like trying to use a Swiss Army knife as a screwdriver. Yes, it can turn a screw but there are much better ways!

As we talked about our Excel frustrations, I decided that all my issues with the Excel have to do with its reliance on the concept of a cell.

I have grown weary of the tyranny of the spreadsheet cell. In a spreadsheet, you have to deal with data on a cell level. Even when I think of the data as an aggregation (ex. matrix, list, vector), Excel forces me to deal with each individual data item when the entire data items is better viewed as an aggregation of data. When I apply a function in Excel, I must explicitly apply the function to each individual data item. While Excel does support some forms of aggregation, like with matrices and named ranges, it almost seems like it was added as an afterthought.

My issues are not limited to the lack of data aggregations – Excel is clumsy to use with complex numbers. I recently had to deal with impedance calculations involving a massive number of complex number calculations in Excel – it worked, but it was extremely painful. The same calculations in Mathcad took a couple of simple statements – the brevity was because (1) Mathcad handles complex numbers as easily as real numbers, and (2) Mathcad handles data aggregations as easily as it does individual numbers.

However, Excel does have its strengths – it provides an excellent demonstration of the power of an integrated data analysis environment. The ability to gather data, process the data with automation (i.e. VBA), and to display the data makes for a powerful tool. The power of tool is limited, however, by the cell-based data model. I would argue that ability to deal with data aggregations makes the combination of Rstudio and ggplot2 a far superior data processing environment to that of Excel.

Understand that I work with Excel every day. I even occasionally compete in Excel competitions to keep my skills up. But I do not try to use it to solve every problem. Excel is great for small data sets of real numbers with a tabular structure – it also does well with lists of strings. For almost anything else, Mathcad, Mathematica, or R then become my tools of choice.

Save

Save

Save

Save

Save

This entry was posted in software. Bookmark the permalink.

8 Responses to The Tyranny of the Spreadsheet Cell

  1. Jesse C. McKeown says:

    "programming spreadsheet cells is manual memory management."

  2. Ronan Mandra says:

    Check out for VBA to support Complex Numbers: http://pfadintegral.com/articles/complex-numbers-vba/

    For aggregations of large sets, MS Access does a good job.

  3. Ronan Mandra says:

    Mark, I wish you had an edit function for our comments. I had meant to say in the above, that there are VBA routines to work with complex numbers. Obviously, if you have a large dataset of complex numbers that same VBA program could be ported to MS Access. I do agree that it's better to work with complex numbers in an mathematical environment that had complex number capabilities built in.

    • mathscinotes says:

      I do not know of an edit function in WP – I will ask. I will change it to whatever you want and delete the old comment. The best approach may be for you to make a comment that says exactly what you want and then I will delete the old one. I will be online all day today.

      mark

    • mathscinotes says:

      I just loaded a comment edit plugin. See if it works for you. I am hoping I can customize it.

      • Ronan Mandra says:

        Mark, thanks for checking into an edit feature for WP; however, I don't see an edit link. I suspect that one would have to log into a session to edit one's own comments otherwise anyone's comments could be edited. I believe your suggestion about you just deleting the older comment per our request will probably be best for now.

        • mathscinotes says:

          Hi Ronan,

          The plug-in only allows editing for five minutes after it has been submitted. I will try to find one with a longer period.

          mark

Comments are closed.