Honest question: for what sorts of problems and requirements are complex spreadsheet analyses the right tool? As opposed to, say, using the data analysis and visualization capabilities of something like R or Python?
I am thinking it can help people that don't have a background in computer science but are tasked to analyze data (for example, a marketing manager for a company). Spreadsheet have a lower entry point (and a lower ceiling) than a regular statistical analysis suite.
Also, using a spreadsheet has a visual component that you lose with R/python that can be off-putting if you are not comfortable with visualizing what certain lines of coding do.
That makes sense. But then I guess my question becomes: at what point are you spending so much time and effort learning advanced features of spreadsheets that you could have become proficient in one of the tools with a higher ceiling?
In many offices most employees have little to no access to programming tools, with good reason (from an IT Support point of view). However, many of these same people will have Excel, and it does what many of those users need it to do. In many companies (outside the IT department), in order for a new tool to gain traction you'd have to find a use-case that made it compelling for the average office worker that wasn't already being met by the existing tools they know how to use, both to encourage people to learn how to use it, and to encourage IT Support to roll it out.
Among other things, spreadsheets allow you to see and fix one-off errors caused by bad data. A "real" programming language is great when you have clean data, but the real world is messy and businesses generally don't devote enough effort to cleaning data. So the person doing the analysis usually has to both scrub the data and do the calculations.
I mean, you have to look at, validate, and clean your data no matter how you chose to analyze it. Any tool, whether it uses a "real" programming language or not, is going to need to support that to be useful at all, and unless your data is "fits in one or two screens of a spreadsheet" scale, I don't see how they would be better in this regard.
Spreadsheets are useful for data exploration. Can you do data exploration with other tools? Sure you can, but spreadsheets make exploring data intuitive and there's value to be had from that. I'd suggest it's not a good idea to get too hung up on any one tool, most of the time people just want to use the one that gets the job done with a minimum of fuss. Sometimes that'll be a script, sometimes not. Sometimes that'll be a spreadsheet, sometimes not.
If you want to know the answer, check out Data Smart[1]
It's a book about doing data science using Excel, until it doesn't make sense using Excel anymore. KMeans, Naive Bayes, Regression, etc. all in Excel, without totally abusing it.
Shared corporate model between half a dozen people, non of whom would know how to use it in R or Python.
A ton of inputs from different departments, monthly updates, etc.
If there's a good web-based tool/package for bringing this stuff into R/Python, I'm listening! (And I don't mean shiny- you need a way to look/edit the inputs too)
Ah yes, that makes good sense. And that helps answer the question I asked in response to pacbard's comment: Even if only one person fully understands all of the complex stuff that's happening "under the hood" of the spreadsheet, many people can contribute. Thanks!
Comments
Honest question: for what sorts of problems and requirements are complex spreadsheet analyses the right tool? As opposed to, say, using the data analysis and visualization capabilities of something like R or Python?
I am thinking it can help people that don't have a background in computer science but are tasked to analyze data (for example, a marketing manager for a company). Spreadsheet have a lower entry point (and a lower ceiling) than a regular statistical analysis suite.
Also, using a spreadsheet has a visual component that you lose with R/python that can be off-putting if you are not comfortable with visualizing what certain lines of coding do.
That makes sense. But then I guess my question becomes: at what point are you spending so much time and effort learning advanced features of spreadsheets that you could have become proficient in one of the tools with a higher ceiling?
In many offices most employees have little to no access to programming tools, with good reason (from an IT Support point of view). However, many of these same people will have Excel, and it does what many of those users need it to do. In many companies (outside the IT department), in order for a new tool to gain traction you'd have to find a use-case that made it compelling for the average office worker that wasn't already being met by the existing tools they know how to use, both to encourage people to learn how to use it, and to encourage IT Support to roll it out.
Among other things, spreadsheets allow you to see and fix one-off errors caused by bad data. A "real" programming language is great when you have clean data, but the real world is messy and businesses generally don't devote enough effort to cleaning data. So the person doing the analysis usually has to both scrub the data and do the calculations.
I mean, you have to look at, validate, and clean your data no matter how you chose to analyze it. Any tool, whether it uses a "real" programming language or not, is going to need to support that to be useful at all, and unless your data is "fits in one or two screens of a spreadsheet" scale, I don't see how they would be better in this regard.
Spreadsheets are useful for data exploration. Can you do data exploration with other tools? Sure you can, but spreadsheets make exploring data intuitive and there's value to be had from that. I'd suggest it's not a good idea to get too hung up on any one tool, most of the time people just want to use the one that gets the job done with a minimum of fuss. Sometimes that'll be a script, sometimes not. Sometimes that'll be a spreadsheet, sometimes not.
Suggesting spreadsheets are useful for messy data is scary. Spreadsheets are associated with a bunch of errors.
If you want to know the answer, check out Data Smart[1]
It's a book about doing data science using Excel, until it doesn't make sense using Excel anymore. KMeans, Naive Bayes, Regression, etc. all in Excel, without totally abusing it.
[1] https://www.amazon.com/Data-Smart-Science-Transform-Informat...
Shared corporate model between half a dozen people, non of whom would know how to use it in R or Python.
A ton of inputs from different departments, monthly updates, etc.
If there's a good web-based tool/package for bringing this stuff into R/Python, I'm listening! (And I don't mean shiny- you need a way to look/edit the inputs too)
Ah yes, that makes good sense. And that helps answer the question I asked in response to pacbard's comment: Even if only one person fully understands all of the complex stuff that's happening "under the hood" of the spreadsheet, many people can contribute. Thanks!
It never hurts to know as many tools as possible for data analysis, as they all have their advantages and disadvantages.