To calculate relative standard deviation in Google Sheets, enter =STDEV.S(A2:A9)/AVERAGE(A2:A9) and format the cell with Format > Number > Percent. For eight daily control results of 98.4, 101.2, 99.7, 100.5, 97.9, 102.3, 100.1 and 99.2 mg/dL, the formula returns 0.014469, which displays as 1.45%.
Sheets has no RSD function, but it has features that make the calculation easier than in many spreadsheet tools: ranges that grow as you add data, BYROW and BYCOL for whole tables, and a TO_PERCENT function. This guide covers each, along with blanks, errors and locale settings.
The Google Sheets RSD Formula
All versions of the formula divide a standard deviation by an average. The difference is only how the result is displayed.
| Goal | Formula | Result |
|---|---|---|
| RSD, then apply Percent format | =STDEV.S(A2:A9)/AVERAGE(A2:A9) | 1.45% |
| RSD already formatted as a percent | =TO_PERCENT(STDEV.S(A2:A9)/AVERAGE(A2:A9)) | 1.45% |
| RSD as a plain number | =STDEV.S(A2:A9)/AVERAGE(A2:A9)*100 | 1.45 |
| Population RSD | =STDEV.P(A2:A9)/AVERAGE(A2:A9) | 1.35% |
TO_PERCENT is specific to Google Sheets. Google describes it as equivalent to choosing Format > Number > Percent, so the formula carries its own formatting wherever it is copied.
Worked Example: Daily Control Results
Type a header in A1 and the eight results in A2:A9. Then build the calculation in steps:
- In C2, enter
=AVERAGE(A2:A9). The mean is 99.9125. - In C3, enter
=STDEV.S(A2:A9). The sample standard deviation is 1.44562. - In C4, enter
=C3/C2. The result is 0.014469. - With C4 selected, choose Format > Number > Percent, or press Ctrl+Shift+5. The cell shows 1.45%.
The two decimal places come from the Percent format. Use the decrease and increase decimal buttons on the toolbar to show more or fewer.
STDEV, STDEV.S, STDEVP and STDEV.P in Sheets
Google Sheets accepts both the older and the newer function names. In Google’s function list, STDEV.S is described as “See STDEV” and STDEV.P as “See STDEVP”, so each pair returns identical results.
| Function | Same as | Divisor | Use for RSD when |
|---|---|---|---|
| STDEV or STDEV.S | Each other | n − 1 | Replicates, lab and quality data, any sample |
| STDEVP or STDEV.P | Each other | n | The data is the complete population |
| STDEVA | n − 1 | Rarely; counts text as 0 |
For most RSD work, use STDEV.S or STDEV. They give 1.45% for the example above, while STDEV.P gives 1.35%. The gap grows as the number of values falls. The sample vs population standard deviation guide explains which one fits your data.
Using STDEV.S rather than STDEV has one practical advantage: the same formula works unchanged if the sheet is ever downloaded and opened in Excel.
Format as a Percent Without Scaling Twice
Percent format multiplies the stored value by 100 for display, exactly as in other spreadsheet programs. A cell containing 0.014469 shows 1.45%. A cell containing 1.4469 shows 144.69%.
So choose one approach:
- a ratio formula with Percent format or TO_PERCENT, or
- a formula ending in *100 with a normal number format.
If an RSD column shows values above 100% for data that obviously agrees closely, the formula probably multiplies by 100 and the cell is also formatted as a percent.
Ranges That Grow as You Add Data
Google Sheets lets you leave off the last row number, so a range such as A2:A runs from row 2 to the bottom of the sheet:
=STDEV.S(A2:A)/AVERAGE(A2:A)
This is useful for control results logged over time. With the eight values above, the formula returns 1.45%. When the next day’s result, 103.8, is typed into A10, the RSD updates to 1.87% without editing the formula.
Two cautions apply. Start the range below the header row: a text header would be ignored anyway, but a numeric header, such as a year, would be counted as data. And keep notes or totals out of the column below the data, since any number there becomes part of the RSD.
RSD for Several Columns at Once With BYCOL
Suppose three production lots are tested five times each, with one lot per column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Test | Lot A | Lot B | Lot C |
| 2 | 1 | 99.1 | 97.8 | 101.3 |
| 3 | 2 | 100.4 | 98.2 | 100.2 |
| 4 | 3 | 98.7 | 99.5 | 102.6 |
| 5 | 4 | 100.9 | 97.1 | 99.8 |
| 6 | 5 | 99.6 | 98.9 | 101.1 |
BYCOL applies a LAMBDA to every column in a range and returns one result per column. In B8, enter:
=BYCOL(B2:D6, LAMBDA(col, STDEV.S(col)/AVERAGE(col)))
The formula fills B8:D8 with 0.91%, 0.95% and 1.08% once the row is formatted as a percent. Add a fourth lot in column E and extend the range to B2:E6, and a fourth result appears. Writing B2:D instead of B2:D6 also picks up extra rows as more tests are added.
The same result is possible by writing the ordinary formula in B8 and dragging it across to D8. BYCOL is simply easier to maintain, because there is one formula to check instead of one per column.
RSD for Every Row With BYROW
Many laboratory sheets list samples down the rows with replicates across the columns. BYROW handles that layout. With triplicates in B2:D4:
| Sample | Rep 1 | Rep 2 | Rep 3 | RSD |
|---|---|---|---|---|
| S-01 | 5.12 | 5.08 | 5.15 | 0.69% |
| S-02 | 12.4 | 12.7 | 12.5 | 1.22% |
| S-03 | 0.83 | 0.86 | 0.81 | 3.02% |
In E2, enter one formula for the whole column:
=BYROW(B2:D, LAMBDA(r, IF(COUNT(r)<2, "", STDEV.S(r)/AVERAGE(r))))
The IF test returns an empty string for rows that have fewer than two numbers, so the empty rows below your data stay blank instead of filling with errors. Each new sample row gets an RSD as soon as its second replicate is entered.
Why ARRAYFORMULA does not work here
ARRAYFORMULA is the usual way to make a Sheets formula run down a whole column, but it cannot give a row-by-row standard deviation. STDEV.S collapses whatever range it receives into a single number, so ARRAYFORMULA(STDEV.S(B2:D4)) returns one standard deviation for all nine values. BYROW solves this by calling the calculation once per row.
RSD for One Group in a Long List
If the data arrives as one row per result, with a sample ID in column A and a value in column B, use FILTER to pick out one group. LET avoids repeating the FILTER:
=LET(v, FILTER(B2:B, A2:A="S-02"), STDEV.S(v)/AVERAGE(v))
Replace “S-02” with a cell reference, such as A2:A=F2, to build a summary table next to a list of sample IDs.
Blanks, Text, Zeros and Errors
Google’s STDEV documentation states that cells containing text inside a referenced range are ignored. Empty cells are skipped in the same way. Zeros are real numbers and are included.
That makes a missing result very different from a zero. Leaving a missing replicate blank calculates the RSD from the remaining values. Typing 0 in its place adds a false measurement and can raise the RSD dramatically. Mark rejected or missing results in a separate comments column rather than with a zero.
Google also documents that STDEV returns #DIV/0! if it receives fewer than two values. A mean of zero produces the same error from the division. You can hide errors with IFERROR, whose second argument is optional in Sheets. Leaving it out returns a blank cell:
=IFERROR(STDEV.S(A2:A)/AVERAGE(A2:A))
A COUNT test is more transparent, because it only hides the “not enough data” case and still shows any other error that needs attention:
=IF(COUNT(A2:A)<2, "", STDEV.S(A2:A)/AVERAGE(A2:A))
Near-zero and negative means. Sheets will calculate an RSD of several hundred percent, or a negative RSD, without complaint. When results sit close to zero or can be negative, such as blanks or baseline-corrected signals, the ratio stops being a meaningful precision measure. Report the standard deviation instead. See limitations of RSD for the reasons.
Rounding: A Sheets-Specific Trap
Percent format only changes the display, so the cell keeps the full-precision ratio for later formulas. If you do need a stored, rounded value, use ROUND with an explicit number of places:
=ROUND(STDEV.S(A2:A9)/AVERAGE(A2:A9), 4)
That stores 0.0145, which displays as 1.45%. Be careful not to leave out the second argument. In Google Sheets the number of places is optional and defaults to zero, so =ROUND(STDEV.S(A2:A9)/AVERAGE(A2:A9)) rounds 0.014469 to 0 and the cell shows 0.00%. Excel would reject the same formula for missing an argument, so this mistake is easy to make in Sheets and hard to spot.
A Rolling RSD for a Growing Log
For control results logged daily, a rolling RSD of the most recent results shows whether precision is changing. With results in column A starting at row 2, enter this in B11 and fill it down:
=IF(COUNT(A2:A11)<10, "", STDEV.S(A2:A11)/AVERAGE(A2:A11))
Because the references are relative, each row down looks at the ten results ending on that row. A sudden rise in the rolling RSD is often the first sign of a change in the method, reagents or instrument. Choose the window to suit how often you measure. Ten results is a reasonable starting point.
Locale, Separators and Decimal Commas
Formula punctuation in Google Sheets follows the spreadsheet’s locale, which you can check under File > Settings (Google’s locale help). In locales that write decimals with a comma, formulas use semicolons between arguments:
=ROUND(STDEV.S(A2:A9)/AVERAGE(A2:A9); 4)
Array literals change as well. Google’s array documentation notes that in comma-decimal locales, the comma that separates columns in an array is replaced by a backslash. If a formula copied from a website shows a parse error, the locale is the first thing to check.
Save It as a Named Function
Sheets lets you store a LAMBDA as a named function under Data > Named functions. Create a function called RSD with one argument placeholder, such as range, and the definition STDEV.S(range)/AVERAGE(range). You can then type =RSD(A2:A9) anywhere in that spreadsheet, and named functions can be imported into other spreadsheets from the same panel.
BYCOL and BYROW accept a named function in place of a LAMBDA, so =BYROW(B2:D4, RSD) also works once the function exists.
Differences From Excel That Actually Matter
Most RSD formulas work in both programs. These are the differences worth knowing, based on each product’s documentation:
| Topic | Google Sheets | Excel |
|---|---|---|
| STDEV vs STDEV.S | Aliases of the same function | STDEV is a compatibility function; STDEV.S added in Excel 2010 |
| Open-ended ranges | A2:A runs to the end of the sheet | Use a Table or a fixed range |
| Percent helper function | TO_PERCENT() | Apply Percent Style formatting |
| IFERROR second argument | Optional, returns blank | Required |
| BYCOL, BYROW, LAMBDA | Available in Sheets | Excel 2024 and Microsoft 365 only |
| Percent shortcut | Ctrl+Shift+5 | Ctrl+Shift+% |
For Excel-specific methods such as AGGREGATE, structured table references and the Name Manager, see How to Calculate Relative Standard Deviation in Excel.
Check Your Result
Paste the same values into the RSD Calculator, choose Sample (n − 1), and compare the mean, standard deviation and %RSD with the three cells you built in the worked example. If the RSD matches but the standard deviation does not, check whether the sheet uses STDEV.P. If nothing matches, check COUNT for text or blank cells that the formula skipped.
Frequently Asked Questions
Is STDEV the same as STDEV.S in Google Sheets?
Yes. Google's function list describes STDEV.S simply as 'See STDEV', and both calculate the sample standard deviation. STDEV.S exists mainly so formulas written for Excel work unchanged. Likewise, STDEV.P and STDEVP are the same population function.
Why does my RSD formula show #DIV/0! in Google Sheets?
Google documents that STDEV returns #DIV/0! when it receives fewer than two values. The same error appears if the average is zero. Check how many numbers the range contains with COUNT, and remember that cells holding text are ignored.
How do I show RSD as a percentage in Google Sheets?
Leave the formula as a ratio, =STDEV.S(A2:A9)/AVERAGE(A2:A9), then choose Format > Number > Percent or press Ctrl+Shift+5. You can also wrap the formula in TO_PERCENT. Do not multiply by 100 as well, or the result will be 100 times too large.
Can Google Sheets calculate RSD for every row automatically?
Yes. BYROW applies a LAMBDA to each row of a range, so one formula such as =BYROW(B2:D, LAMBDA(r, IF(COUNT(r)<2, "", STDEV.S(r)/AVERAGE(r)))) returns an RSD for every row, including rows you add later.
Why does Google Sheets reject my formula with commas?
The argument separator follows the spreadsheet locale. In locales that use a comma as the decimal mark, formulas use semicolons between arguments instead. Check or change the locale under File > Settings.
