Google Sheets adds the ability to display trend lines on xy scatter graphs
At some point in the recent past Google Sheets added from OpenOffice/LibreOffice the statistical functions including the t-distribution functions TINV, TDIST, and TTEST. Automated histograms appeared recently as a Google Sheets chart type. Google Sheets box and whisker plots apparently landed last February.
Along with the statistical functions came a full Google Sheets LINEST array function capable of calculating confidence intervals for slopes and intercepts.
The last missing piece was the ability to display trend lines on a graph, and that piece landed at some recent point in time.
Click on a data value and there is an add trend line button with trend line type options.
Options include linear, exponential, and polynomial at present.
Click on the trend line to add the coefficient of determination R squared to the legend. Note that the chart has to be "large enough" to display the full legend for R squared to appear.
Although I have not yet seen an option to display the equation on the graph, the SLOPE, INTERCEPT, and LINEST functions provide all of the necessary tools to find the equation of the line.
While "doing" statistics in spreadsheets is known to be problematic, and R is now the heavy lifter for getting statistics calculated in statistics classes around the world, my students are non-majors some of whom are working with computers for the first time and almost all of whom are working in English as a second language. Google Sheets also simplifies students working in groups on shared data and shared presentations of data, a key feature of the current course. None of the data crunching in the class is of the complexity or data set size that would require the capability of R, hence the class continues to use spread sheets as a vehicle for learning statistical calculations. R would also, generally speaking, require downloading R, possibly an R GUI, and packages to both institutional and student computers. Google Sheets, however, runs on everything without downloads, including in a more limited fashion on cell phones using the Google Sheets app. Coupled with already extant integration with the Schoology LMS, and Google Sheets is the strongest choice for the course at this time.
A separate spreadsheet includes running a slope calculation against an expected value, calculating the t-statistic and p-value for that t-statistic all in Google Sheets. The summer 2016 tab contains these calculations.
Along with the statistical functions came a full Google Sheets LINEST array function capable of calculating confidence intervals for slopes and intercepts.
The last missing piece was the ability to display trend lines on a graph, and that piece landed at some recent point in time.
Click on a data value and there is an add trend line button with trend line type options.
Options include linear, exponential, and polynomial at present.
Click on the trend line to add the coefficient of determination R squared to the legend. Note that the chart has to be "large enough" to display the full legend for R squared to appear.
Although I have not yet seen an option to display the equation on the graph, the SLOPE, INTERCEPT, and LINEST functions provide all of the necessary tools to find the equation of the line.
While "doing" statistics in spreadsheets is known to be problematic, and R is now the heavy lifter for getting statistics calculated in statistics classes around the world, my students are non-majors some of whom are working with computers for the first time and almost all of whom are working in English as a second language. Google Sheets also simplifies students working in groups on shared data and shared presentations of data, a key feature of the current course. None of the data crunching in the class is of the complexity or data set size that would require the capability of R, hence the class continues to use spread sheets as a vehicle for learning statistical calculations. R would also, generally speaking, require downloading R, possibly an R GUI, and packages to both institutional and student computers. Google Sheets, however, runs on everything without downloads, including in a more limited fashion on cell phones using the Google Sheets app. Coupled with already extant integration with the Schoology LMS, and Google Sheets is the strongest choice for the course at this time.
A separate spreadsheet includes running a slope calculation against an expected value, calculating the t-statistic and p-value for that t-statistic all in Google Sheets. The summer 2016 tab contains these calculations.
Comments
Post a Comment