Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
A 200-day moving average in Excel or Google Sheets is usually a simple average of the latest 200 observations, not the values falling within the last 200 calendar days. To calculate it, put each dated value on its own row, average the current value and the previous 199 values, then chart that column beside the original series. This guide uses the 200-observation definition; for daily stock data, those observations commonly represent trading sessions, since weekends and market holidays have no price row.
The same method works for any consistently recorded numeric series. The important decisions are to define what one observation means, keep the rows in time order, wait for a complete 200-value window, and label the result so nobody confuses a row-count average with a calendar-date filter.
As an Amazon Associate I earn from qualifying purchases.
Recommended Free Tools
What does a 200-day moving average measure?
A 200-observation simple moving average (SMA) is the arithmetic mean of the current observation and the 199 observations immediately before it. Each new row advances the window by one observation: the oldest value drops out, the newest value enters, and the mean is recalculated.
For a price series, you choose which price field to average, such as closing price. The same calculation works for other numeric time series, including a construction project’s daily labor hours, material usage, or completed quantities, provided the data is recorded consistently. A moving average smooths short-term fluctuations so the overall pattern is easier to see; Microsoft describes that purpose as making the “pattern or trend more clearly” visible in its guidance on predicting data trends. Smoothing is not a forecast or a guarantee of what comes next.
Popular on Amazon
- Ergonomically Designed: Work in tight areas with a compact design that gets into tough spots
- Both tools are designed to fit into difficult to reach spaces
- Complete a wide range of applications with a high-performance motor that delivers up to 300 UWO, and an impact driver delivering up to 1,400 in-lbs of torque
- Both the DEWALT impact driver and drill driver feature integrated LED work lights with a convenient 20-second delay, ensuring enhanced visibility in dimly lit or challenging work areas
- One-Handed Loading - Keep one hand free with a 1/4 in. hex chuck that accepts 1 in. bit tips
Think of the result as a summary attached to the last row in each window. The value in the row dated March 31, for example, represents the 200 observations ending on that row, under the definition used here. It is not the average of every possible value in a fixed historical period, and it is not a second version of the source data.
Does “200-day” mean 200 trading sessions or 200 calendar days?
For this guide, “200-day” means 200 observations in the spreadsheet. With one row per available stock-price observation, that usually means the latest 200 trading sessions—not a filter for the 200 calendar dates ending today. Weekends and market holidays do not add price observations, so 200 sessions may span more than 200 calendar days.
The distinction matters for any dated series. A 200-observation window counts rows or values; a 200-calendar-day window asks which dates fall inside a date range. If observations are irregular or absent on some dates, the two methods can produce different averages. A calendar-day lookback may contain fewer than 200 observations, and it is not what the row-based formula below calculates.
Choose the definition that matches the question you need to answer and label the result accordingly. For example, if each row is one trading-session close, “200-observation average” is precise. If the intended measure is explicitly a calendar-day window, define that date range separately rather than assuming that a 200-row formula will enforce it. A Google Sheets community discussion illustrates this distinction; it is community guidance, not an authoritative market standard.
This also prevents a common reporting error: calling a line “200-day” when the source actually contains weekly, monthly, or event-based rows. The period always counts the observations represented by the rows. If the rows are monthly, the same setting is 200 months; if rows mix daily and weekly records, there is no consistent 200-period meaning until the data is standardized.
How should I set up the spreadsheet?
Use one row per intended observation, with dates in chronological order and the numeric series in a single column. Add a third column for the calculated average.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →| Column A | Column B | Column C |
|---|---|---|
| Date | Price or value | 200-observation average |
| Observation date | Numeric value for that date | Formula result |
Put headers in row 1, so the first observation is in row 2. Sort the rows oldest to newest; otherwise, a rolling window will follow the row order rather than the time sequence. Check that column B contains numeric values, not numbers stored as text, and decide how you will handle missing observations. The examples below assume one valid numeric observation per row and no duplicated dates.
In a construction context, use a clearly defined measure and cadence. A daily average of recorded labor hours is not interchangeable with an average of weekly hours, and mixing units or project phases in one series can make the resulting line difficult to interpret. The formula does not know what the values represent; it simply averages the numbers in its window.
Also popular on Amazon
- Valued Carpenter Pencil Set: You will get 2 pcs solid carpenter pencils with 26 piece 2.8 mm refills, 1 replaceable sharpener, 1 plastic storage box.The complete carpenter pencils combination allows you to finish your work faster and more easily
- Deep Hole Marker Pencil: The deep-hole construction pencils adopts 45mm elongated tip design, which is more convenient to mark in the small hole or in other tight areas that other carpenter markers cannot reach
- Carpenter Pencils with Sharpener: The sharpener is screwed into the top of the work pencil, which won't get lost either. Built-in pencil sharpener that keep the lead with pointed and smooth to Improves line of sight in fine work
- Stronger Solid Lead: This work pencil is matched with a 2.8 mm thick lead , which is much thicker and stronger during the drawing process of construction work, it will not break or damage easily
- Marks on Various Surfaces: 3 colors solid construction pencil can marks on various surfaces,such as metal, plastic, wood, paper etc. Ideals for woodworkers, contractors, craftsmen, builders, merchants and masons
Before calculating, check the date column for duplicate rows, reversed dates, and accidental gaps. A blank date with a number, or a date with a text-formatted number, can make the chart and the calculation tell different stories. Keep the source field consistent as well: closing prices, adjusted closes, labor hours, and completed quantities are different series and should not be silently combined.
Give the helper column a descriptive heading rather than just “MA.” “200-observation average” states both the method and the window. If the source is a stock close, “200-session closing-price SMA” is even clearer. A precise heading remains useful when the column is copied into another workbook or included in a report without its surrounding explanation.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →How do I calculate the rolling average with a formula?
Enter a formula at the row containing the 200th value. If the first numeric value is in B2, the first complete window is B2:B201, so the first result belongs in C201.
- In C1, enter a descriptive header such as
200-observation average. - In C201, enter
=IF(COUNT(B2:B201)=200,AVERAGE(B2:B201),NA()). - Fill or copy the formula down column C for the remaining rows.
The formula checks that the window contains 200 numeric values. If it does, AVERAGE calculates their arithmetic mean; if not, NA() marks the result as unavailable rather than presenting a partial average as a full 200-observation average. Google’s AVERAGE function documentation says that the function returns the numerical average and ignores text values.
When you fill the formula down, the next window shifts by one row: B3:B202, then B4:B203, and so on. This is the rolling part of the calculation. The output in each row represents the window ending on that row’s date, so the first calculated value is aligned with the 200th observation—not with the first date in the window.
If every row in the range is guaranteed to have a numeric value, the shorter formula =AVERAGE(B2:B201) also works. The guarded version is safer when gaps or text may occur, because it verifies that each window contains 200 numbers. Keep in mind that COUNT counts numeric cells, not calendar days or dates. The formula’s window still moves through 200 rows; it does not search backward for the 200 most recent numeric values if some rows are blank.
That last point is easy to miss. Suppose one row in B2:B201 is blank. The range still has 200 rows, but only 199 numeric values. The guarded formula returns NA(), while an unguarded AVERAGE would ignore the blank and calculate a mean from the values that remain. That shorter mean may be useful for another purpose, but it should not be labeled a complete 200-observation average.
Likewise, a number stored as text is not counted by COUNT. If a result is unexpectedly unavailable, inspect the cell format and the imported data rather than deleting the check. Correcting the source value preserves the intended window and makes later audits easier.
Also popular on Amazon
- 【Great Compatibility】This Katerk 1/4 inch hex shank bit holder is specifically designed for 1/4 inch hex shank drill bits. It's compatible with most 1/4 fast hex handles, hex sockets, various electric screwdrivers, and handheld screwdrivers. The bit holder makes it a valuable addition for any handyman.
- 【Secure and Safe】Built with a secure backup nut design, each drill bit holder securely locks onto your bits, ensuring they stay firmly in place. Additionally, our bit holder incorporates a high-quality steel ball rolling design that holds up to several kilograms of weight, ensuring your various drill bits don't fall off.
- 【Easy One-Handed Operation】The bit holder for impact driver allows you to change bits single-handedly, simplifying your workflow. Its multi-color design further allows for quick identification of the drill bit you need.
- 【Compact and Convenient】Thanks to its compact size, this 1/4 inch bit holder is easy to carry around. The bit holder allows for easy attachment to various tools, making this a convenient addition to your construction accessories. The Katerk bit holder is cast from high-quality alloy material, promising a long product lifespan. Despite its rugged strength, the bit holder remains lightweight, making it portable.
- 【Cool Christmas Gift For Men Stocking Stuffers】 This screwdriver bit holder, driver bit holder, impact bit holder, can be given as a gift to your loved one, especially for anyone involved in construction or electrical work. It's a must-have for stocking stuffers for men and women, tools gifts for dad, tech gadgets for men, gifts for dad, gifts for him, gifts for husband, gifts for boyfriend, cool gadgets for men, and cool gifts for dad.
For another starting row, change both the first formula cell and the range. The rule is constant: include the current observation and exactly the preceding 199 rows, then copy downward with relative references. Do not widen the range to 201 rows to compensate for a header, and do not include the date column in the arithmetic range.
How do I make a chart with the average and the original data?
Plot the date column as the horizontal axis and include both the source series and the moving-average series. That lets readers see the underlying fluctuations and the smoothed line together, with the average beginning only when a full window is available.
In Excel
- Select the dated data and the two value series, including the headers—typically columns A through C from row 1 to the last populated row.
- Insert a line chart. Confirm that Date is used for the horizontal axis and that both the original value and the average appear as series.
- Check the legend and label the calculated series clearly, such as
200-observation average.
If the chart is already in place, the key is to add the average column as another series rather than replacing the original values. Adjust the chart’s selected data range if the new column is not included.
In Google Sheets
- Select the relevant date and value ranges, including the average column and headers.
- Choose Insert > Chart.
- In the Chart editor’s Setup panel, check the data range, chart type, and series. Make sure the date column is assigned to the horizontal axis and both value columns are included.
Google’s chart help documents inserting a chart and changing its data range and chart type in Setup. If the chart does not show the intended lines, revisit that panel and confirm the ranges and series assignments.
In either spreadsheet, the line for the calculated series should not appear before the first complete window. With the guarded formula, the earlier cells contain unavailable values; this makes the gap explicit in the sheet. If the chart renders those cells unexpectedly, check how its data range handles the NA() results and confirm that the average series starts on the correct row.
Keep the original line visible even when it is noisy. The purpose of the chart is comparison: the source line shows individual jumps and reversals, while the 200-period line shows the broader level. A chart containing only the smoothed series can hide the very variation that explains why the average moved.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteUse the same date range for both series. If the average column is selected from C201 while the source starts at B2, the chart may shift points or assign the wrong dates. Selecting complete columns with headers, then checking the Setup or source-data range, helps catch that alignment problem.
Can I add a moving-average trendline directly to the chart?
Yes. Both Excel and Google Sheets document a built-in moving-average chart trendline with a configurable period. This is a quick way to add a visual overlay when you do not need the individual calculated values in worksheet cells.
Also popular on Amazon
- Toolwiz 17-inch magnetic sweeper maintaining a load capacity of up to 8.8 pounds provides a perfect solution to the problem of clearing small ferrous metal objects, such as nails, tacks, bolts, and metal shavings from workshops or garage floors.
- Convenient and portable：17” is good for home, small workshop, or small yard as it is space-saving and more reachable for narrow areas. Perfect for collecting nails or small parts in the car, under the table, and in other nooks or crannies.
- Upgraded Handle Design: The handle features a sturdy, non-telescopic design to completely prevent slipping during use. By simply adding or removing the extension poles, the height can be adjusted to 3 different lengths: 18.9" (48 cm), 32.5" (82.5 cm), and 42.5" (108 cm) from ground to top. This customizable height helps prevent back bending and muscle strain, making your cleaning tasks effortless
- Flexible wheels: Upgrade wheel screws to avoid being too tight to spin or too loose. More comfortable 3-inch rubber wheels roll smoothly on carpet, grass, concrete, and hardwood floors. Just push and pull this rolling magnet-sweeper to pick up metal nails easily.
- Lightweight materials: The magnetic nail sweeper's compact and lightweight design makes the sweeping can be done anywhere you want, Push or pull it under the furniture, passing through the workshop floor, driveway, mud, grass, and gravel.
Excel chart trendline
Select the chart’s data series, add a trendline, choose Moving Average, and set the period to 200. Microsoft’s instructions for adding a trend or moving-average line explain that the plotted moving-average trendline has the series’ total number of points minus the specified period. It therefore begins only after enough points are available for the selected period. Interface labels can vary as Excel changes, so use the chart’s trendline options if the exact menu wording differs.
Google Sheets chart trendline
Double-click the chart, open Customize > Series, enable Trendline, choose the moving-average type, and set Periods to 200. Google documents the trendline controls and period setting in its trendline help. If Trendline is not available, Google notes that it may not be supported for the selected data.
A chart trendline is convenient, but it is less transparent than a formula column: the individual averages are not visible as values beside the source data. If you need to reuse or inspect the numbers, calculate the helper column. If the visual line is all you need, the built-in option is faster. When exact row-by-row alignment matters, calculate the column and compare the chart overlay rather than assuming the built-in trendline uses the same treatment of gaps or source data.
The period control means 200 points from the selected series, not automatically 200 dates on the calendar. If your chart contains irregular observations, the trendline follows the points supplied to it. Verify the cadence and the selected series before describing the result as a 200-session or 200-calendar-day line.
Which method should I use?
| Consideration | Formula helper column | Built-in chart trendline |
|---|---|---|
| Main use | Reusable, inspectable rolling values | Fast visual smoothing line |
| Where the period is set | By the formula’s range width | In the chart’s trendline period control |
| Where results appear | In worksheet cells and, if selected, on a chart | On the chart |
| Best fit | When you want to audit, copy, or use each average elsewhere | When you only need a visual overlay |
| Initial window | You can explicitly require 200 numeric values | Excel documents that the plotted point count is the series point count minus the period |
The two methods share the goal of drawing a smoothed line, but they are not identical workflows. The formula gives you direct control over which values are included and how incomplete windows appear. The trendline minimizes worksheet setup. For a result that needs to be reviewed or reused, the formula’s visible windows make it easier to trace a plotted point back to its source values.
Use the helper column when a reviewer may ask, “Which 200 values produced this point?” It also lets you export the calculated series, apply another formula to it, or compare it with a second period. Use the trendline when the chart is a presentation aid and the underlying values do not need to be delivered separately. If the two lines differ, check the source range, missing values, and first valid point before deciding that one method is wrong.
Free tools Windows power users keep installed
One-click scans. No signup required.
What should I check if the result looks wrong?
- The first result appears too soon: Check that the formula starts in the row of the 200th observation. With the first value in B2, that is row 201. A formula entered earlier may be calculating a shorter window.
- The average is missing or shows an unavailable result: The current 200-row range may contain fewer than 200 numeric values. Check for blank cells, text-formatted numbers, or other nonnumeric entries. The guarded formula intentionally withholds the result unless the numeric count is 200.
- The average seems to skip dates: The formula rolls through rows, not calendar dates. Inspect the date column for missing observations, duplicate dates, or dates out of order, then decide whether the intended method is an observation-count or calendar-day window.
- The plotted line is absent or starts at the wrong place: Confirm that the average column is included as a chart series and that the date column is the horizontal axis. Also check that the range includes the first valid average and does not begin above or below it unintentionally.
- The line changes when you expect it to stay fixed: A rolling average is recalculated for every row. Each new observation changes the window by dropping its oldest value and including the newest one; it is not a single fixed average for the whole dataset.
- The built-in trendline is unavailable: In Sheets, trendline controls may not be offered for the selected data. Check the chart type and series configuration, or use the formula column and chart that series directly.
- The chart shows a line but the cells do not match it: Compare the chart’s selected series and period with the helper-column range. A trendline may be attached to the original series while the visible helper column is based on a different field or range.
- Values are shifted by one row: The first average belongs with the 200th observation. Check that the formula is in C201 when the first value is B2 and that the chart uses column A dates for both series.
For a clean chart, use a descriptive legend label and keep the source series visible. A smoothed line can conceal individual jumps, so the underlying values provide important context rather than clutter to remove.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What does the moving-average line tell me—and what doesn’t it tell me?
It summarizes the recent level of a series across a rolling window and reduces the visual effect of shorter-term variation. Because the window includes past observations only, the plotted value at a date represents the average through that date under the chosen row-based definition.
Also popular on Amazon
- APPLY TO MOST DRILLS : 6 pieces screw extractors work with any drill and all the screw size or bolt (3-12mm), These extractor tools are used to reverse the damaged screw counterclockwise until it is released
- The DIY Tools for Men-Men won't know they need screw extractor set until they have one! This screw extractor set is GIFTS WRAP AVAILABLE, can be gifted to your loved ones as it reflects a fresh trend with fun and practical. Amazing and Unique tool gifts for men women. The must have stocking stuffers for men, gifts for dad, husband, father, him, guy teens, adult, Boyfriend, son on Birthdays, Christmas, Fathers Day or anniversary. Perfect Holiday Gifts Guide
- HIGH QUALITY - This damaged screw remover tool kit is made from High-Speed Steel H.S.S 4341# , which have high hardness of 63hrc, it's durable and easily to remove any damaged screws.
- EASY TO USE: Just two simple steps to get the job done - drill a hole with a cutting size using the right size screw extractor and easily remove the screw or bolt using the removal tool Hardness 63-65HRC: Titanium hardened steel material, more durable than most screw extractors on the market, better hardness and durability than others, buy with confidence and complete peace of mind
- Cool Gadgets for Men Dad Christmas Birthday Gifts for Men and Women - Men won't know they need screw extractor sets until they have one! Every screw has the potential to rust and break, which can create a situation where it cannot be removed. Owning it, there will be no anxiety about rusty screws. This screw extractor set is GIFTS WRAP AVAILABLE, can be gifted to your loved ones as it reflects a fresh trend with fun and practical. Amazing and unique tool gifts for men women. A must have stocking stuffer for men on birthdays, Christmas, Father's Day or anniversaries, a gifts for dads, husbands, fathers, him, adults, boyfriends, sons. The best and most practical and surprising Christmas gifts
A 200-period line reacts more slowly than a shorter-period line because each new observation is only one of 200 values. A sudden jump can therefore appear in the source series immediately while the average moves gradually. That lag is a consequence of the window, not evidence that the calculation is broken.
It does not establish why values changed, predict the next value, or prove that a trend will continue. A 200-observation average is also slow to reflect a sudden change because many prior observations remain in the window. Treat it as a descriptive smoothing measure, and inspect the original data alongside it when timing or individual events matter.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteDo not describe the line as a guarantee, signal, or forecast unless you have a separate method that supports such a claim. The official documentation cited here supports smoothing and charting. It does not establish predictive accuracy or an investment conclusion.
A practical validation checklist
- Confirm that the title identifies the series being averaged, such as closing price or daily labor hours.
- Confirm that dates are oldest to newest and that each row represents one intended observation.
- Verify that the first complete window is current value plus the preceding 199 values.
- Check the first formula row: B2:B201 produces the first result in C201.
- Inspect the numeric count when blanks or imported text are present.
- Make sure the chart includes Date, the original series, and the average series.
- Check the legend and period label so readers know whether the line counts observations or calendar days.
- If using a trendline, verify its selected series and period rather than assuming it matches a helper column.
These checks take less time than explaining a misleading chart later. They also make the workbook easier for someone else to update when new observations are appended.
Frequently Asked Questions
Can a 200-day moving average be calculated from monthly data?
Yes, the same rolling-mean calculation can use any regularly defined observation series. With monthly data, however, a 200-observation window means 200 months, not 200 days; name the period in the units your rows actually represent.
Are 200 days supposed to mean trading days or calendar days?
It depends on the definition you choose. This guide uses 200 observations, which usually means 200 trading sessions when each row is a market observation. A calendar-day lookback is a separate date-range calculation and may contain fewer than 200 observations.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Should I use a simple or weighted moving average for this spreadsheet?
The formulas and chart settings here calculate a simple moving average, which gives each value in the window equal weight. A weighted average is a different calculation and should be labeled separately so readers do not mistake it for the 200-observation SMA.
Why does my chart show a break at the start of the moving-average line?
A full-window average does not exist until the 200th numeric observation, so the earlier portion is intentionally unavailable. That break distinguishes a complete rolling average from a partial average based on fewer values.
Why does COUNT matter in the formula?
COUNT checks that the 200-row window contains 200 numeric cells. It prevents blanks or text from producing an average based on fewer numeric observations while still being labeled as a complete 200-period result.
The Bottom Line
For a row-based 200-period SMA, enter =IF(COUNT(B2:B201)=200,AVERAGE(B2:B201),NA()) in C201, fill it down, and chart Date, the original values, and the average together. This counts 200 observations—not automatically 200 calendar days—so label the series and choose the definition deliberately.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →
