mathvideo•Use AVERAGE and STDEVP for12 monthly temperature values, copy row formulas down and observe automatic updates. The worked change28 to20 updates Taipei mean to22.82 and population standard deviation to5.08.
Use a spreadsheet to compute the mean and population standard deviation of12 monthly temperatures for Taipei, Kaohsiung and Hsinchu. The lesson enters AVERAGE and STDEVP with row ranges and copies their formulas down. Changing Taipei month6 from28 to20 updates its displayed mean from23.48 to22.82 and standard deviation from5.19 to5.08; the linked chart follows. Values are displayed rounded, and the sample/population distinction is introduced without proving the denominator correction.
Generated from the video's visuals and explanation; not verbatim speech.
This segment starts with a Google Sheet, the upper part of the table has been filled with temperature data for months 1 to 12 for the three cities Taipei, Kaohsiung, and Hsinchu. Column N indicates 'Average', column O indicates 'Standard Deviation'; the screen shows the existing results for these columns from the beginning, but the focus of the actual operation is to first establish the average formula.
The speaker enters the equals sign in cell N2, indicating entering formula mode. Then types AVERAGE, the screen shows a function hint, explaining that AVERAGE is used to return the numerical average of the dataset and ignores text. The mathematical object here is a row of monthly data, the goal is to compress the 12 values in that row into one average number.
The formula argument starts from Taipei's January data B2. B2 is located at the intersection of row 2 and month1 data column; the speaker then presses the colon to extend to M2, M2 is located in the month12 data column M of the same row. The colon connects the two corner cells into a rectangular range B2:M2, so the final formula in N2 is =AVERAGE(B2:M2).
After pressing Enter, N2 displays 23.48. This value is the average result of Taipei's temperature data for months 1 to 12; the screen simultaneously retains the formula =AVERAGE(B2:M2), which can verify that the calculation range indeed covers the entire row of monthly data for Taipei.
Next, the speaker explains that Kaohsiung and Hsinchu do not need to re-enter the formula. The cursor moves to the fill handle at the bottom right of N2, dragging down to N3 and N4. Since B2:M2 was not locked, when copying to the next row, the row index increases relatively, and the formula automatically becomes =AVERAGE(B3:M3) and =AVERAGE(B4:M4).
After copying, N3 displays25.57 and N4 displays23.13, the rounded annual means for Kaohsiung and Hsinchu. The standard-deviation column has been cleared for the later demonstration; its initial5.19,3.80,5.55 results will be rebuilt with a different function.
The screen continues from the temperature table where averages were completed previously. The speaker transitions to standard deviation, first reminding to know the English function name, and starts typing S T D E.
At this time, the autocomplete list shows multiple standard deviation-related functions. The speaker specifically points out that besides the difference of "with text and without text," it is more important to distinguish between "sample" and "population."
The speaker explicitly states that this section uses population standard deviation, while sample standard deviation involves sampling contexts; as for why they differ, this video does not expand, leaving it for subsequent videos.
Then, =STDEVP(B2:M2) is entered in O2, applying the population standard deviation function to Taipei's data for months 1 to 12, with the calculation result displayed as 5.19.
After completing the first row, the speaker fills down the O2 formula to O3 and O4. The population standard deviations for Kaohsiung and Hsinchu are obtained as 3.80 and 5.55 respectively, completing the standard deviation calculation for all three datasets at once.
The video then switches to the line chart generated from the same table. The legend includes Taipei, Kaohsiung, Hsinchu; the horizontal axis is months, and the vertical axis is monthly average temperature, used to explain that the next lecture will introduce how to plot tables into line charts.
To demonstrate real-time interaction, the speaker changes Taipei's month6 cellG2 from 28 to 20. On the screen, N2's mean immediately changes from 23.48 to 22.82, and O2's standard deviation changes from 5.19 to 5.08.
When switching back to the line chart, the blue Taipei curve clearly dips at the position of month 6, directly showing that the chart changes in real time as the table data updates.
In Google Sheets, first enter the equals sign in the target cell, then enter the AVERAGE function name. The return value of AVERAGE is the average of numeric data within the argument range, text will be ignored. This example enters =AVERAGE(B2:M2) in N2, where B2:M2 covers Taipei months1 to12, a total of12 temperature data points, after execution N2 displays 23.48.
The screen hint window displays AVERAGE(value1, [value2, ...]). Value 1 is the first value or range that must be included in the calculation; value 2 and subsequent arguments are optional and can repeat. The summary states the function 'Returns the numerical average value in the dataset, ignoring text', the example is AVERAGE(A2:A100, B2:B100).
The colon in the formula connects the start and end points of the range. B2:M2 indicates the rectangular range from B2 to M2; in this table, it is row2, columns B to M, containing12 months of data. The speaker once said semicolon but immediately corrected to colon, the actual input is also a colon.
When dragging the formula from N2 along the fill handle at the bottom right down to N3 and N4, unlocked row references will change with the target row. Therefore, =AVERAGE(B2:M2) becomes =AVERAGE(B3:M3) and =AVERAGE(B4:M4) after copying, separately calculating the averages for Kaohsiung and Hsinchu, resulting in 25.57 and 23.13.
The opening table shows standard deviations5.19,3.80,5.55. The first part rebuilds only the AVERAGE formulas; later, STDEVP is entered to rebuild the population standard deviations. Recognize the difference between the statistics and choose the corresponding function.
Before entering the function, the video emphasizes that there is more than one standard deviation-related function, and conceptually one must distinguish between sample and population. The speaker explains that this section uses population standard deviation, while sample standard deviation involves sampling; why they differ is left for subsequent videos.
In Google Sheets, the video selects STDEVP to calculate the standard deviation of the entire population. The demonstration formula is =STDEVP(B2:M2), where B2:M2 is the data range for Taipei months 1 to 12, and the calculation result is 5.19. The specified12 months are treated as the whole dataset. As a mathematical clarification, population variance averages squared deviations from the dataset mean, and population standard deviation is its nonnegative square root. These descriptive calculations do not require the months to be independent.
After completing the formula for the first row, filling down O2 to O3 and O4 allows applying the same method to Kaohsiung and Hsinchu. The screen results are sequentially 3.80 and 5.55, showing that the same statistical formula can be quickly applied to data rows with identical structures.
The video demonstrates changing Taipei's month6 cellG2 from 28 to 20. N2's mean changes from 23.48 to 22.82, and O2's standard deviation changes from 5.19 to 5.08; the blue Taipei line in the line chart also clearly dips at month 6, showing that the chart is directly linked to table data.
Explore conditions, steps and evidence. Supplementary explanations are labeled separately from content shown in the video.
A built-in function in Google Sheets used to calculate the average of numeric data within specified arguments or ranges, while ignoring text.
Cell N2 input =AVERAGE(B2:M2).
The function hint window displays AVERAGE(value1, [value2, ...]), with the summary 'Returns the numerical average value in the dataset, ignoring text.'
AVERAGE
A built-in function in Google Sheets used to calculate the average of numeric data within specified arguments or ranges, while ignoring text.
Arguments are one or more numbers, cell references, or cell ranges; this segment actually uses B2:M2, B3:M3, B4:M4.
Connects the start and end points in a cell reference to indicate a rectangular range.
The formula changes from =AVERAGE(B2 to =AVERAGE(B2:M2).
:
Connects the start and end points in a cell reference to indicate a rectangular range.
Used between two cell references, such as B2:M2.
The cell containing Taipei's January temperature data, also the top-left corner of the full-year data range for Taipei.
B2 is located at the intersection of row 2 'Taipei' and month1 data column, with a value of 16.8.
B2
The cell containing Taipei's January temperature data, also the top-left corner of the full-year data range for Taipei.
Single cell reference.
The cell containing Taipei's December temperature data, also the bottom-right corner of the full-year data range for Taipei.
M2 is located at the intersection of row 2 'Taipei' and month12 data column, with a value of 16.5.
M2
The cell containing Taipei's December temperature data, also the bottom-right corner of the full-year data range for Taipei.
Single cell reference.
The formula in N2 is =AVERAGE(B2:M2).
During selection, B2 to M2 spans12 months, marked by an orange dashed box.
B2:M2
A rectangular range consisting of 12 temperature data points for Taipei from January to December.
Row 2, columns B to M.
After executing =AVERAGE(B2:M2), N2 displays 23.48.
Column N header is 'Average'.
N2
The result cell for the average temperature of Taipei from January to December.
Numerical result; screen shows 23.48.
The range of 12 temperature data points for Kaohsiung from January to December.
After copying down, the formula in N3 is =AVERAGE(B3:M3), resulting in 25.57.
B3:M3
The range of 12 temperature data points for Kaohsiung from January to December.
Row 3, columns B to M.
The range of 12 temperature data points for Hsinchu from January to December.
The formula in N4 is =AVERAGE(B4:M4), resulting in 23.13.
B4:M4
The range of 12 temperature data points for Hsinchu from January to December.
Row 4, columns B to M.
Column O header is 'Standard Deviation', O2:O4 already display 5.19, 3.80, 5.55.
The statistical measure column name pre-listed in the table for this segment, but the actual operation only demonstrated average calculation.
The early passage has not yet entered a standard-deviation formula; STDEVP appears later.
\sigma
The source column label means standard deviation. The Greek letter here is editorial notation for that statistic; the video uses the written column title.
Column O; no function formula provided in this segment.
The user types =STDE in O2, and the autocomplete list shows STDEV, STDEVA, STDEVP, STDEVPA; STDEVP is finally selected.
The function name for calculating the population standard deviation in Google Sheets.
STDEVP
The function name for calculating the population standard deviation in Google Sheets.
Applies to numerical data within the specified range B2:M2.
The formula bar and cell content show =STDEVP(B2:M2).
The temperature data range for Taipei from month 1 to 12, used as the argument for STDEVP.
B2:M2
The temperature data range for Taipei from month 1 to 12, used as the argument for STDEVP.
A continuous cell range in the spreadsheet.
N2 displays the Taipei average value of 23.48.
23.48
The average temperature of Taipei over 12 months, visible on screen as a previously calculated result.
Mean of temperature data.
First enter the equals sign in the target cell to activate the formula, then enter AVERAGE as the average function name; next connect the start and end points of the range with a colon, for example B2:M2, representing the 12 months of data in that row. After pressing Enter, the function returns the average of the numeric data within the range.
N2 input =AVERAGE(B2:M2), getting 23.48; after copying down, N3 is =AVERAGE(B3:M3), N4 is =AVERAGE(B4:M4).
The Google Sheets interface displays three rows of data for Taipei, Kaohsiung, and Hsinchu, with column N as Average and column O as Standard Deviation.
The speaker verbally mentioned standard deviation, but this segment did not actually demonstrate the standard deviation function.
First enter the equals sign in the target cell to activate the formula, then enter AVERAGE as the average function name; next connect the start and end points of the range with a colon, for example B2:M2, representing the 12 months of data in that row. After pressing Enter, the function returns the average of the numeric data within the range.
The target cell must first have the equals sign entered to enter formula mode.
The arguments for AVERAGE can be numbers, cell references, or cell ranges.
The range connects the top-left and bottom-right cells with a colon.
The function ignores text and only averages numeric data.
The hint window displays AVERAGE(value1, [value2, ...]).
The example in the hint window is AVERAGE(A2:A100, B2:B100).
The AVERAGE function returns the numerical average of the dataset and ignores text. In the syntax, value 1 is a required argument, and arguments after value 2 are optional and can repeat; each argument can be a single value or other values or ranges included in the calculation.
The AVERAGE function returns the numerical average of the dataset and ignores text. In the syntax, value 1 is a required argument, and value 2 and subsequent arguments are optional and can repeat; each argument can be a single value or other values or ranges included in the calculation.
Value 1 is the first value or range to include when calculating the average.
Values 2... are optional arguments and can repeat.
Text will be ignored, and the calculation object is numeric data.
The video first points out that standard deviation function names are similar, but conceptually one must distinguish between "sample" and "population." The speaker explicitly states that this section uses population standard deviation, while sample standard deviation involves sampling contexts; why they differ is left for subsequent videos.
The autocomplete list simultaneously shows STDEV, STDEVA, STDEVP, STDEVPA, where STDEVP corresponds to population standard deviation.
The video first points out that standard deviation function names are similar, but conceptually one must distinguish between "sample" and "population." The speaker explicitly states that this section uses population standard deviation, while sample standard deviation involves sampling contexts; why they differ is left for subsequent videos.
This segment only actually uses the population standard deviation function.
The difference between sample and population standard deviation is not proven or derived via formulas in this segment.
Enter =STDEVP(B2:M2) in O2.
The operation method is to first enter the equals sign and the function name STDEVP in the target cell, then select the population standard deviation function from the autocomplete list, and finally specify a continuous data range as the argument using a colon. The video demonstrates applying this function to the Taipei row B2:M2.
The function hint window displays STDEVP(value1, [value2, ...]), summarized as "Calculates the standard deviation based on the entire population."
The operation method is to first enter the equals sign and the function name STDEVP in the target cell, then select the population standard deviation function from the autocomplete list, and finally specify a continuous data range as the argument using a colon. The video demonstrates applying this function to the Taipei row B2:M2.
This demonstration references a continuous range of numeric cells; STDEVP can also accept other numeric arguments.
This segment demonstrates a single row of data B2:M2.
This is a descriptive calculation for the specified nonempty numeric dataset treated as the full population. It uses the mean of squared deviations followed by the nonnegative square root; no independence assumption between months is required.
After selecting O2, dragging down the fill handle automatically fills O3 and O4 with results 3.80 and 5.55.
After completing the standard deviation formula for the first row, one can use the spreadsheet's fill handle to drag downwards, applying the relative range formula to the rows below, obtaining standard deviations for multiple datasets at once.
After completing the standard deviation formula for the first row, one can use the spreadsheet's fill handle to drag downwards, applying the relative range formula to the rows below, obtaining standard deviations for multiple datasets at once.
Applicable when adjacent rows have the same positional structure.
In this segment, filling down from O2 to O3 and O4.
The speaker claims that the standard deviation learned at this stage of the course is population standard deviation.
This is a statement of curriculum context, not a mathematical proof.
The speaker claims that the standard deviation learned at this stage of the course is population standard deviation.
Based on the teaching context in the video.
Regarding the content of "what we are learning now in first-year high school."
The speaker points out that the context of sample standard deviation is related to sampling.
The video does not provide the formula or complete definition of sample standard deviation here.
The speaker points out that the context of sample standard deviation is related to sampling.
The discussion object is sample standard deviation, not population standard deviation.
General explanation regarding "sample standard deviation."
The speaker explicitly indicates that this segment does not explain why population standard deviation and sample standard deviation are different.
The speaker explicitly indicates that this segment does not explain why population standard deviation and sample standard deviation are different.
Limited to the current video segment.
Regarding "these two," i.e., population standard deviation and sample standard deviation.
N2 sequentially inputs =A, =AV, =AVE, =AVERAGE, =AVERAGE(, =AVERAGE(B2, =AVERAGE(B2:, =AVERAGE(B2:M2).
B2:M2 is selected by an orange dashed box, covering Taipei row data for months 1 to 12.
After Enter, N2 displays 23.48.
Establish the formula in N2, using B2:M2 as the data range for Taipei from January to December.
The screen shows the target cell is N2, and the selected range is B2:M2; AVERAGE is used to find the average of numeric data.
After executing the formula, N2 displays Taipei's full-year average temperature of 23.48.
After Enter, the screen cellN2 displays23.48.
The average temperature for Taipei from January to December is calculated by =AVERAGE(B2:M2), and the screen result is 23.48.
The narration describes dragging the fill handle down to copy the formula; the actual handle is below-right.
The original formula in N2 is =AVERAGE(B2:M2); after copying, N3 is =AVERAGE(B3:M3), and N4 is =AVERAGE(B4:M4).
After moving the cursor to the cross at the bottom right of N2 and dragging down, N3 and N4 are filled with 25.57 and 23.13 respectively.
The starting formula is located in N2, referencing B2:M2 in row 2.
The screen displays the content in the formula bar for N2.
After copying to N3, the range row index changes from 2 to 3, corresponding to the Kaohsiung row data.
The screen shows the formula in N3 is =AVERAGE(B3:M3), and the result is 25.57.
Continuing to copy to N4, the range row index changes from 2 to 4, corresponding to the Hsinchu row data.
The screen shows the formula in N4 is =AVERAGE(B4:M4), and the result is 23.13.
Cell ranges without absolute reference symbols will shift relatively as the formula copy position changes, so the same formula can separately calculate the full-year average for each city.
Entering =STDEVP(B2:M2) in O2 yields 5.19.
The population standard deviation of Taipei's 12-month temperatures is calculated as 5.19 in this segment.
Establish the population standard deviation formula in O2, with arguments being the Taipei data range for months 1 to 12.
The video directly demonstrates function input and range selection.
After formula calculation, O2 displays the population standard deviation of Taipei data.
The screen result is directly visible.
The population standard deviation of Taipei's 12-month temperatures is calculated as 5.19 in this segment.
After filling down O2, O3 displays 3.80, and O4 displays 5.55.
The population standard deviations for the three cities are sequentially Taipei 5.19, Kaohsiung 3.80, Hsinchu 5.55.
Use the fill handle to apply the same method to city rows below.
The animation directly shows the drag-fill process.
Kaohsiung and Hsinchu population standard deviations are obtained respectively.
Screen values are directly visible.
The population standard deviations for the three cities are sequentially Taipei 5.19, Kaohsiung 3.80, Hsinchu 5.55.
After changing G2 from 28 to 20, N2 changes from 23.48 to 22.82, and O2 changes from 5.19 to 5.08.
When switching to the line chart, the blue Taipei line clearly dips at month 6.
The video uses an example of changing a value to explain: as long as the original data changes, the mean, standard deviation obtained from formulas, and the line chart generated from the table will all update in real time.
Directly modify the original data for Taipei's 6th month.
Screen and narration demonstrate synchronously.
Taipei's mean value updates as the data changes.
Screen value changes are directly visible.
Taipei's population standard deviation also updates as the data changes.
Screen value changes are directly visible.
Corresponds to the drop in position of the Taipei curve at month 6, showing the chart reflects new data synchronously.
Can be directly observed after the animation switches to the chart.
The video uses an example of changing a value to explain: as long as the original data changes, the mean, standard deviation obtained from formulas, and the line chart generated from the table will all update in real time.
The table row headers are Taipei, Kaohsiung, Hsinchu, columns 1 to 12 are monthly data, column N is Average, column O is Standard Deviation.
N2 input =AVERAGE(B2:M2) gets 23.48; N3 is =AVERAGE(B3:M3) gets 25.57; N4 is =AVERAGE(B4:M4) gets 23.13.
The screen shows the worked spreadsheet values for this example.
The values in the standard deviation column O2:O4 existed at the beginning of the segment, but this segment did not demonstrate its calculation formula.
In Google Sheets, based on the monthly temperature data for Taipei, Kaohsiung, and Hsinchu, calculate the average temperature for each city from January to December.
Taipei data is located in B2:M2.
Kaohsiung data is located in B3:M3.
Hsinchu data is located in B4:M4.
Column N is the average result column.
Column O is the standard deviation column, but this segment did not demonstrate its calculation.
Use the AVERAGE function to obtain the respective averages for the three rows of city data, and utilize copying to avoid re-entering formulas for each row.
Enter the average formula in N2, the range covers Taipei data for months 1 to 12.
AVERAGE returns the average of numeric data; the colon indicates the range from B2 to M2.
After pressing Enter, the average for Taipei is obtained.
The screen shows the result in N2 is 23.48.
Copy the formula from N2 down to N3, the range automatically changes to the Kaohsiung row.
The screen shows the formula in N3 is =AVERAGE(B3:M3), and the result is 25.57.
Continue copying to N4, the range automatically changes to the Hsinchu row.
The screen shows the formula in N4 is =AVERAGE(B4:M4), and the result is 23.13.
The average for Taipei is 23.48, the average for Kaohsiung is 25.57, and the average for Hsinchu is 23.13.
In the screen, N2:N4 display 23.48, 25.57, 23.13 respectively, and the formula bar corresponds to displaying the B to M column ranges for each city.
The table title is "Temperature Statistics Data," rows are Taipei, Kaohsiung, Hsinchu, columns are months 1 to 12, plus "Average" and "Standard Deviation."
O2 uses =STDEVP(B2:M2), result 5.19; after fill-down O3=3.80, O4=5.55.
The screen shows the worked spreadsheet values for this example.
Calculate the population standard deviation for each of the three sets of 12-month temperature data for Taipei, Kaohsiung, and Hsinchu.
Taipei B2:M2 = 16.8, 16.5, 18.9, 22.5, 25.2, 28, 30.5, 30.2, 29.7, 24.7, 22.3, 16.5
Kaohsiung B3:M3 = 19.5, 20.3, 22.6, 25.9, 27.8, 29.2, 30.3, 29.1, 29.5, 27.2, 25.2, 20.2
Hsinchu B4:M4 = 15.6, 15.4, 18, 22.3, 25.3, 28.3, 30.4, 29.9, 29.6, 24.7, 22, 16
Mean column already shows N2=23.48, N3=25.57, N4=23.13
Find the population standard deviation for the three datasets in the standard deviation column O2:O4.
First enter the population standard deviation function and data range for the Taipei row.
The video demonstrates selecting STDEVP and using B2:M2 as arguments.
Obtain the population standard deviation for Taipei.
Screen calculation result is directly visible.
Apply the same formula to the Kaohsiung and Hsinchu rows.
Animation shows dragging the fill handle.
Obtain the population standard deviations for Kaohsiung and Hsinchu.
Screen results are directly visible.
Taipei 5.19, Kaohsiung 3.80, Hsinchu 5.55.
The video does not perform independent verification, only using spreadsheet calculation results and screen display as verification.
After changing G2 from 28 to 20, N2 becomes 22.82, O2 becomes 5.08.
In the line chart, the blue Taipei line clearly dips at the position of month 6.
The screen shows the worked spreadsheet values for this example.
Demonstrate whether the line chart generated from the table changes in real time when the original table data is modified.
Original G2 = 28
Modified G2 = 20
Chart is plotted based on table data, legend includes Taipei, Kaohsiung, Hsinchu
Observe the changes in mean, standard deviation, and line chart after modifying one Taipei data point.
Directly edit the temperature for Taipei's 6th month.
Screen and narration demonstrate synchronously.
Taipei's mean value updates automatically.
Screen value changes are directly visible.
Taipei's population standard deviation updates automatically.
Screen value changes are directly visible.
After switching to the chart, the blue Taipei line drops at the position of month 6.
Animation directly shows chart update.
After modification, Taipei's mean becomes 22.82, standard deviation becomes 5.08, and the Taipei curve in the line chart clearly dips at month 6.
Verification is done by observing the synchronous changes of formula results and graphics on the screen.
The screen is a Google Sheet, the upper table contains three rows for Taipei, Kaohsiung, Hsinchu, columns 1 to 12 are monthly data, column N is Average, column O is Standard Deviation; below there is another blank template table.
Google Sheet worksheet
Three rows of data for Taipei, Kaohsiung, Hsinchu
Columns 1 to 12
Average column N
Standard Deviation column O
Blank template table below
The cursor moves near the completed average and standard deviation values.
After inspecting the existing results in N2:N4 and O2:O4, the cursor moves to N2 preparing to enter a formula.
The order of month columns is fixed from 1 to 12.
Each city's data is located in the same row.
Column N corresponds to Average, column O corresponds to Standard Deviation.
The screen first presents a complete temperature statistics table, allowing viewers to identify data rows, month ranges, and output columns for average and standard deviation.
N2 sequentially shows =A, =AV, =AVE, =AVERAGE, =AVERAGE(, =AVERAGE(B2, =AVERAGE(B2:, =AVERAGE(B2:M2).
The function hint window displays the syntax, summary, and examples for AVERAGE.
When selecting B2:M2, the data for months 1 to 12 in the Taipei row is marked with an orange dashed box.
N2 cell
Formula editing bar
AVERAGE hint window
B2:M2 range
Orange dashed selection box
The formula is gradually completed from the equals sign to the AVERAGE function.
The hint window first displays function name candidates, then displays AVERAGE syntax and description.
B2:M2 is selected and added to the formula.
The target cell for the formula remains N2 throughout.
The data range is located in row 2 for Taipei.
The range columns go from B to M, corresponding to months 1 to 12.
The visual process shows the sequence of establishing the formula: first activate the formula, then select the function, and finally specify the data to be averaged with a colon range.
The cursor moves to the bottom right corner of N2, the pointer becomes a cross.
After dragging down, N3 and N4 are filled with 25.57 and 23.13 respectively.
When clicking N3, the formula bar shows =AVERAGE(B3:M3); when clicking N4, the formula bar shows =AVERAGE(B4:M4).
N2 bottom right fill handle
N3 cell
N4 cell
Formula bar
Kaohsiung row B3:M3
Hsinchu row B4:M4
The formula in N2 is copied to N3 and N4.
The range row index changes with the target row: B2:M2 becomes B3:M3, then becomes B4:M4.
N3 and N4 display 25.57 and 23.13 respectively.
The column range remains B to M.
The function remains AVERAGE.
Each row still calculates the average for months 1 to 12 for that city.
Dragging down demonstrates relative copying: row references in the formula shift with the cell position, so the same formula can be applied to different city rows.
After entering =STDE in O2, a candidate list pops up on the right showing STDEV, STDEVA, STDEVP, STDEVPA, with Chinese descriptions.
The formula bar eventually becomes =STDEVP(
Cell O2
Autocomplete list
Formula bar
Input expands from =STDE to =STDEVP(
Candidate list continuously displays different standard deviation function names and descriptions
Main table data B2:M4 unchanged
Mean values in column N unchanged
The screen uses the function candidate list to emphasize that there is more than one standard deviation-related function, and labels STDEVP as "standard deviation of the entire population."
After selecting O2 and dragging down, O3 and O4 are filled with 3.80 and 5.55 respectively.
Cells O2:O4
Fill handle
O3, O4 change from blank to numerical results
Original temperature data B2:M4 unchanged
Mean values N2:N4 unchanged
Visually explains that the same formula method can be quickly applied to multiple adjacent data rows.
Screen switches to line chart, title area shows "Monthly Average Temp," legend includes Taipei, Kaohsiung, Hsinchu; horizontal axis is months 1 to 12, vertical axis approx 15 to 30.
Line chart
Three colored curves
Legend
Month horizontal axis
Temperature vertical axis
Switch from table view to chart view
Data source remains the three rows of temperature data above
The graphic converts monthly temperatures into time-series curves, facilitating comparison of trends among the three cities.
After modifying G2 and switching back to the chart, the blue Taipei line clearly dips at month 6.
Blue Taipei line
Data point at month 6
Taipei point at month 6 drops from high position to near 20
Kaohsiung and Hsinchu curves do not change due to this modification
This visual change directly corresponds to G2 changing from 28 to 20, explaining that the chart reflects table data updates in real time.
This segment actually uses a colon to connect the start and end points, for example B2:M2; semicolons in formulas are usually used to separate multiple arguments, not to indicate a single continuous range.
The actual range separator symbol entered is a colon, the formula is =AVERAGE(B2:M2).
When establishing a cell range, one might mistakenly press semicolon.
The demonstrated continuous cell range uses a colon, as in B2:M2. This lesson does not establish a universal argument-separator rule for all spreadsheet locales.
You can use the fill handle at the bottom right of the cell to copy downwards; if references are not locked, the range will shift relatively with the target row.
The formulas in N3 and N4 are copied from N2 and become =AVERAGE(B3:M3) and =AVERAGE(B4:M4).
When calculating averages for multiple similar rows, one needs to re-enter the complete formula for each row.
You can use the fill handle at the bottom right of the cell to copy downwards; if references are not locked, the range will shift relatively with the target row.
Analyst supplement: The visible operations in this segment are only establishing and copying down the AVERAGE formula; although the standard deviation column displays values, the calculation formula was not shown within the segment.
The opening table lists standard deviations5.19,3.80,5.55; these are cleared while the mean formulas are rebuilt.
The early passage does not give a standard-deviation formula; its role is clarified by the later STDEVP demonstration.
Since the title and opening mention standard deviation, one might mistakenly think this segment has already demonstrated the standard deviation function.
The first part establishes and copies only AVERAGE formulas; the later STDEVP demonstration computes population standard deviations. The two functions address different statistics.
The video reminds through the candidate list and narration that these functions involve differences of "with text and without text" as well as "sample and population"; this section selects the population standard deviation STDEVP.
Autocomplete list simultaneously lists STDEV, STDEVA, STDEVP, STDEVPA.
When seeing multiple functions starting with STDEV, it is easy to think they are just different names with the same usage or meaning.
The video reminds through the candidate list and narration that these functions involve differences of "with text and without text" as well as "sample and population"; this section selects the population standard deviation STDEVP.
The video clearly separates the two and points out that why they are different is not explained in this segment, avoiding direct confusion here.
Beginners may think population standard deviation and sample standard deviation are just different names.
The video clearly separates the two and points out that why they are different is not explained in this segment, avoiding direct confusion here.
The method uses =AVERAGE(B2:M2), and the hint window simultaneously displays AVERAGE(value1, [value2, ...]).
The method for calculating averages directly applies the AVERAGE function syntax, using B2:M2 as the value 1 range argument.
In the example problem, N2, N3, N4 separately use the AVERAGE formula to calculate three rows of data.
The city temperature example demonstrates the operational method of first establishing the formula with AVERAGE, then finding the row average with a range argument.
Relative copying is an extension step of this average method, allowing the same formula to be applied to other city rows.
=AVERAGE(B2:M2) becomes =AVERAGE(B3:M3) and =AVERAGE(B4:M4) after copying.
Relative copying is an extension step of this average method, allowing the same formula to be applied to other city rows.
Column N is Average, column O is Standard Deviation.
Only the AVERAGE formula appears in the segment, no standard deviation formula appears.
The initial passage describes averages; the later passage explicitly uses STDEVP for standard deviation.
The mean describes central tendency; standard deviation describes dispersion. The lesson demonstrates AVERAGE first and STDEVP afterward.
One must first understand that this section deals with population standard deviation to understand why STDEVP is chosen instead of other standard deviation functions.
One must first understand that this section deals with population standard deviation to understand why STDEVP is chosen instead of other standard deviation functions.
The STDEVP formula in O2 is filled down to O3 and O4.
Fill down itself is not a new statistical concept, but rather quickly applies the established STDEVP calculation method to other city rows.
Line chart legend matches table city names.
Chart changes immediately after modifying G2.
The line chart is a visualization built directly on the temperature table data, so changes in table data are immediately reflected in the graphic.
N2 starts entering from the equals sign, finally becoming =AVERAGE(B2:M2).
What does the colon in B2:M2 represent in the formula?
B2 and M2 are connected by a colon to form B2:M2.
How does the AVERAGE function handle text data?
The AVERAGE hint window states 'Returns the numerical average value in the dataset, ignoring text.'
The narration describes dragging the fill handle down to copy the formula; the actual handle is below-right.
N2 contains =AVERAGE(B2:M2); copying to N3 and N4 changes the respective ranges to B3:M3 and B4:M4.
Did this segment actually demonstrate standard deviation calculation?
The first calculation establishes the mean formulas; the standard-deviation formula is introduced later.
AVERAGE is introduced first, followed later by STDEVP.
=STDEVP(B2:M2) appears in O2.
Which function should be used in Google Sheets to calculate population standard deviation?
Candidate list shows STDEV, STDEVA, STDEVP, STDEVPA.
Why does the screen show STDEV, STDEVA, STDEVP, STDEVPA, but the video chooses STDEVP?
After dragging O2 down, O3 and O4 automatically show results.
After changing G2 to 20, N2, O2, and the line chart change synchronously.
Where is the difference between population standard deviation and sample standard deviation?
Covered · Opening explanation theme is calculating average and standard deviation using spreadsheets, screen presents temperature statistics table and existing average, standard deviation columns.
Covered · Demonstrates entering equals sign in N2, selecting AVERAGE, viewing function hints, and specifying the range for Taipei January to December with B2:M2.
Covered · After executing the formula, N2 displays the Taipei average of 23.48.
Covered · Uses the fill handle at the bottom right of N2 to copy the formula down, N3 and N4 get Kaohsiung 25.57 and Hsinchu 23.13 respectively.
Covered · The completed mean formulas are checked; the standard-deviation column is blank, ready for the next calculation.
Covered · Screen continues from previous segment's mean results, narration begins introducing standard deviation function names.
Covered · Speaker distinguishes sample and population, and explains this section uses population standard deviation.
Covered · Enter =STDEVP(B2:M2) and get Taipei result 5.19.
Covered · Fill down to get Kaohsiung 3.80, Hsinchu 5.55.
Covered · Narration summarizes that computer calculation of large amounts of decimal standard deviations is easy, screen still stays on the completed table.
Covered · Switch to line chart, previewing next lecture will introduce plotting from table and real-time interaction.
Covered · Modify G2 to 20, mean, standard deviation, and chart update in real time.
Covered · Narration concludes, saying the operation is actually quite simple, screen still shows the updated line chart.
Use a spreadsheet to compute the mean and population standard deviation of12 monthly temperatures for Taipei, Kaohsiung and Hsinchu. The lesson enters AVERAGE and STDEVP with row ranges and copies their formulas down. Changing Taipei month6 from28 to20 updates its displayed mean from23.48 to22.82 and standard deviation from5.19 to5.08; the linked chart follows. Values are displayed rounded, and the sample/population distinction is introduced without proving the denominator correction.
Bilibili7:03Prerequisite in Probability and statistics: Expectation → Variance.
Expectation · View learning sequence
YouTube2:01Next in Probability and statistics: Variance → Normal distribution.
Normal distribution · View learning sequence
YouTube0:51Next in Probability and statistics: Variance → Normal distribution.
Normal distribution · View learning sequence
YouTube0:41Explanation of Variance, complementing the current application.
Variance
YouTube2:21Explanation of Variance, complementing the current application.
Variance
YouTube1:58Explanation of Variance, complementing the current application.
Variance