Google Sheets Calculations: A Guide

Last Updated on August 16, 2023 by Jake Sheridan

In this guide, you will learn all about calculations in Google Sheets, calculating from time & date, statistics, trend analysis and more.

25 Google Sheets Calculations How-Tos

Diving into the maze of Google Sheets calculations can seem like a…

Wait a minute, I think AI wrote this intro. No human actually dives into a maze. You foolish robot, better luck next time. Let me write the rest with fleshy human hands… 🤖

Whether you’re scheduling tasks, crunching numbers, or predicting trends, Google Sheets has got you covered.

How To Calculate (Almost) Anything in Google Sheets

Below you will find 25 step-by-step instructions for calculating (almost) everything in Google Sheets.

Each tutorial is grouped under these categories to make it a little easier to navigate:

  • Basic Calculations
  • Advanced Calculations
  • Time and Date Calculations
  • Data Comparison and Difference Calculation
  • Statistical Analysis in Google Sheets
  • Graphing and Trend Analysis
  • Keeping Track of Calculations

Basic Calculations in Google Sheets

Learn the fundamental formulas and methods to perform basic mathematical operations such as sum, average, percentage, and tallying in Google Sheets

How to Calculate Sum

  • You need to have a clean and tidy group of data to work with.
  • Insert the formula =SUM(A3:A8) to calculate the sum for the data group.
  • Copy the example spreadsheet to see how it is done.

More detailed tutorial: How To Calculate Sum In Google Sheets

How to Calculate Average

  • The mean of a data set is the sum of all numbers in the dataset, divided by the number of data points.
  • The median of a data set is the middle number if all numbers are ranked in order, or the mean of the two middle numbers if there are an even number of numbers.
  • The mode of a dataset is the number that appears most frequently in the data set.

More detailed tutorial: How To Calculate Average On Google Sheets

How to Calculate Percentage

  • There are a few ways to visualize data using percentages.
  • You can use percentages to visualize a proportion, over a total, or changes between data.
  • To show percentages in Google Sheets, select the cell, then select ‘Format’, then select ‘Number’, then select ‘Percent’.

More detailed tutorial: How To Calculate Percentage In Google Sheets

How to Increase Cell Value by 1

  • Incrementing a cell value by 1 is a common task when working with data in Google Sheets.
  • Google Sheets makes it easy to increase cell values by 1 using a simple formula or the AutoFill tool.
  • You can use the Fill Handle tool to copy the formula down the column.

More detailed tutorial: How To Increase Cell Value By 1 in Google Sheets

How to Tally

  • Create a separate tab in Google Sheets to enter tally data.
  • Use checkboxes to represent each tally.
  • Use the COUNTIF formula to sum up the tally count.

More detailed tutorial: How To Tally In Google Sheets

Advanced Calculations in Google Sheets

This section covers how to perform more complex statistical calculations like standard deviation, variance, standard error, p-value, and z-score using Google Sheets

How to Calculate Standard Deviation

  • The formula used to calculate standard deviation is the STDEV() formula.
  • The reference to the range of data to be used in the formula is added as a parameter.
  • The standard deviation will recalculate any time the data in the input range is updated.

More detailed tutorial: How To Calculate Standard Deviation In Google Sheets

How to Calculate Variance

  • Variance of a sample can be calculated using the VAR() function in Google Sheets.
  • Variance of an entire population can be calculated using the VARP() function in Google Sheets.
  • The default number of decimal places for variance calculations can be adjusted in the toolbar.

More detailed tutorial: How To Calculate Variance In Google Sheets

How to Calculate Standard Error

  • A clean and tidy group of data is needed to calculate standard errors in Google Sheets.
  • The formula =STDEV.S(A3:A8) / SQRT(COUNT(A3:A8)) can be used to calculate the standard error.
  • An example spreadsheet is provided to help understand the process.

More detailed tutorial: How To Calculate Standard Error In Google Sheets

How to Calculate P Value

  • The p-value provides the minimal level of significance at which the null hypothesis would be rejected.
  • To calculate P Value in Google Sheets, one needs to insert the formula =T.TEST(A2:A10,B2:B10,2,2).
  • An example spreadsheet is provided to help understand the process.

More detailed tutorial: How To Calculate P Value In Google Sheets

How to Calculate Z Score

  • Z Score is a statistic that indicates how many standard deviations a result deviates from the mean.
  • The formula to calculate Z Score is z = (X – μ) / σ, where X is a single raw data value, μ is the mean of the dataset, and σ is the standard deviation of the dataset.
  • To apply the formula to all of the data, you can simply drag the right-bottom corner downwards.

More detailed tutorial: How To Calculate Z Score In Google Sheets

Time and Date Calculations in Google Sheets

Understand how to effectively manipulate and calculate time, age, and date-related data in your spreadsheets with Google Sheets’ versatile functions.

How to Calculate Time

  • Select the cell you want to calculate the duration in and type the formula “=B2-A2”.
  • To properly format the calculation cell as a duration, select it then open the Format menu and choose Duration.
  • To convert fractions of days to decimal time, multiply by 24 using the formula “=C2*24”.

More detailed tutorial: How To Calculate Time In Google Sheets

How to Calculate Age

  • The DATEDIF function can be used to calculate age.
  • The YEARFRAC function can be used to calculate age.
  • An example spreadsheet is provided to help understand the process.

More detailed tutorial: How To Calculate Age In Google Sheets

How to Calculate Dates

  • The DAYS, DATEDIF, and NETWORKDAYS functions can be used to count the number of days between two dates.
  • The DAYS and DATEDIF functions count all days, while the NETWORKDAYS function excludes Saturdays and Sundays.
  • A clean and tidy group of data is needed to calculate dates in Google Sheets.

More detailed tutorial: How To Calculate Dates In Google Sheets

How to Calculate Days Between Dates

  • The DAYS, DATEDIF, and NETWORKDAYS functions can be used to count the number of days between two dates.
  • The DAYS and DATEDIF functions count all days, while the NETWORKDAYS function excludes Saturdays and Sundays.
  • An example spreadsheet is provided to help understand the calculations.

More detailed tutorial: How To Calculate Days Between Dates in Google Sheets

Data Comparison and Difference Calculation in Google Sheets

Explore various ways to compare data and calculate differences between columns, as well as finding the highest value among your data points.

How to Calculate Difference

  • You can calculate differences between two different values in Google Sheets by using formulas.
  • You need to have a clean and tidy group of data to work with.
  • You can manually subtract the two values using the minus sign ‘-’.

More detailed tutorial: How To Calculate Difference In Google Sheets

How To Find Difference Between Two Columns

  • You can use formulas to calculate the difference between two values in Google Sheets.
  • You can manually subtract the two values using the minus sign ‘-’.
  • You can copy the example spreadsheet to see how it is done.

More detailed tutorial: How To Find Difference Between Two Columns in Google Sheets

How To Find Highest Value

  • Select the entire data group, then select ‘Format’ then select ‘Conditional Formatting’.
  • Use a custom formula to format and color the highest exam result among the students.
  • The highest-scoring student will be highlighted once the formula is applied.

More detailed tutorial: How To Find Highest Value in Google Sheets

Statistical Analysis in Google Sheets

Dive into Google Sheets’ capability to perform statistical analyses like finding correlation, correlation coefficient, and performing regression.

How to Find Correlation

  • Correlation is a statistical relationship between two data sets that may or may not imply causation.
  • The CORRELL() function can be used to quickly find correlation between two data sets.
  • Correlations range from -1 to 1, with greater absolute value implying stronger correlation.

More detailed tutorial: How To Find Correlation In Google Sheets

How to Find Correlation Coefficient

  • The Correlation Coefficient is a statistical tool used to assess the strength of the correlation between two datasets.
  • Correlation coefficients range from -1 to 1, with greater absolute value implying stronger correlation and numbers close to zero implying weak or no correlation.
  • The CORRELL() function in Google Sheets can be used to calculate the correlation coefficient between two datasets.

More detailed tutorial: How To Find Correlation Coefficient In Google Sheets

How to Do Regression

  • Linear regression is a statistical method used to investigate the relationship between a dependent variable and one or more independent variables.
  • The equation for a linear regression line is in the form Y = a + bX where X is the independent variable and Y is the dependent variable.
  • An easy way to find the linear regression line in Google Sheets is to chart the data using a scatter plot.

More detailed tutorial: How To Do Regression In Google Sheets

Graphing and Trend Analysis in Google Sheets

This segment provides insights on how to find slopes, trendline equations, y-intercepts, and generally use Google Sheets for graphical trend analysis.

How to Find Slope

  • Insert a scatter plot in Google Sheets to find the slope of the data.
  • Slope is calculated as the rise divided by the run, or the change in height divided by the interval that change occurs over.
  • The equation for a straight line is y = mx + c, where m is the slope and c is the y intercept of the line.

More detailed tutorial: How To Find Slope In Google Sheets

How to Find Trendline Equation

  • Select the entire data cell, choose Insert, and select Chart.
  • Change the chart title to ‘Enjoyment Metric’ for a better representation of the scatter chart.
  • Select ‘Trendline’ and then select ‘Use Equation’ as your Label.

More detailed tutorial: How To Find Trendline Equation In Google Sheets

How to Find Slope

  • Any two different points on a line can be used to calculate the slope of any line.
  • Select the entire data cell, choose Insert, and select Chart to generate a line chart.
  • Select ‘Series’, then select ‘Trendline’ to insert the slope.

More detailed tutorial: How To Find Slope On Google Sheets

How to Find Y Intercept

  • Select the entire data cell, choose Insert, and select Chart.
  • Change the chart title to ‘Enjoyment Metric’ for a better representation of the scatter chart.
  • Select ‘Trendline’ and then select ‘Use Equation’ as your Label to find the y-intercept.

More detailed tutorial: How To Find Y Intercept In Google Sheets

Keeping Track of Calculations in Google Sheets

Master techniques for maintaining a running total and tracking data over time, providing a dynamic way to monitor your spreadsheet’s calculations.

How to Keep a Running Total

  • Prepare the spreadsheet by entering the numbers in chronological order and adding additional info in columns to the left.
  • Calculate the running total by adding a header to the column beside the number column and entering the formula.
  • Update the formula to only display the running balance for rows with data entered.

More detailed tutorial: How To Keep A Running Total In Google Sheets

Summary

Hopefully this guide has given you an overview of the different calculations Google Sheets can handle.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: