How to Calculate the Bollinger Bands Indicator in Excel

Video showing how to calculate Bollinger Bands using Excel.
▶If you want to see the formulas used in this video, the accompanying article is posted: wp.me/p32qmZ-BL
▶Improve your trading profits with Tradinformed Backtest Models: bit.ly/Backtest...
▶Check out my other Technical Indicator videos: • How to Calculate the S...
▶How to calculate this and other popular indicators are included in my latest eBook, now available in the Amazon Kindle Store: amzn.to/1smFKHr
My ebook course on building backtest models in Excel is also available in the Amazon Kindle Store: amzn.to/15NDaw4
Follow me on Twitter: / tradinformed
www.tradinformed.com

[@hturtyada9435]

I didn't come to see how they are calculated in Excel, but this vid explains Bolliger Bands in general so well!!

[@HO-ri6hh]

Hi Mark, you have a mistake, you run stdev formula for 19 data instead of 20, please check it?

[@WeirdMedicine]

beautiful, much easier than I thought it might be! thanks!

[@ZvikoMutsindiri]

Mark, you started with cell F4 for the SMA but for the Upper and Lower bands start with the cell F5. Did i miss something or it was just a small mistake.

[@faisalabusulaiman588]

Mark, 7 years later and this information is amazing! Thank you!

[@MarkUrsell]

Glad it was helpful!

[@mohamedal-dabbagh8710]

Well done Mark. Thanks a million. I never knew the trick of F4 to convert to absolute notation and double click on the right down corner to fill the below cells!
HOWEVER,
A mistake you made, when you started to calculate STDEVPA, is that you overlooked the first row of data due to freezing the upper label 3 rows. So, you actually ignored row no. 4. Thereby, you considered 19 rows instead of 20.

[@Mjall2]

This is the guy taking our money

[@ghattassaliba4910]

great work, thanks

[@GiulioBragaMusic]

Hello Mark, and thank you. I've tried to understand when the market is ranging using your help. Firstly, observing the charts, more or less, it seemed to me that in a M1 chart, it takes not more twenty minutes (usually less) to go from the lowest to the highest value of that 20 period. Let's say we are using your spreadsheet, in the row 23 I calculate the ABSolute value of the difference between the 20th highest and the 1st lowest value: Then I thought a needed a SMA of that. Therefore, the first useful value of this calculation comes at your row 43, where I wrote a SMA function of those values, and starting from that row, on another column I'm highlighting all the values that stay below an arbitrary value of 120 points (that I think it's a consistent "ranging" value). I'm writing to you to know if there's another way to get the result to know: 1) mathematically when the market is in range mode if my approach is wrong, 2) what is the correct "ranging" value, because I feel it should be more dynamic, sometimes it could stay higher or lower, maybe in connection with the Bollinger Bands value. 3) how to get the correct average period to L to H (instead of arbitrary 20 I've used) 4) how to get the difference between ranging mode, and "dead" mode - Thank you for your attention

[@fredsalfa]

Thanks ! - I found that using STDEV.S rather than STDEVPA was more relevant to what my Trading Platform was using.

[@MarkUrsell]

+Alfa Won Thanks for commenting. That is an interesting point. STDEV.PA is used to measure the standard deviation of the whole population, whereas STDEV.S is for a sample of the population. STDEV.S will return a higher value and therefore mean that the bands will be wider. Based on John Bollinger's book I believe that he originally intended to use the STDEV.PA calculation. But overall I would expect that both versions of the calculation will be effective.

[@sammostanbet9889]

Thank You Mark
Very useful

[@MarkUrsell]

Thanks for the comment Sam

[@g1kluang]

Hi Mark, thanks for the video. Just curious, why people use different values of standard deviation to calculate the bollinger bands? What are the effects if we use different values of the std? Thanks

[@ahmadaminfilms]

Just reading this on mathsisfun.com and thought it may be helpful "The Standard Deviation is a measure of how spread out numbers are."

[@LilConniePromotions]

How would I calculate the bbands of 1 minute chart instead of 20 days. Or even a 5 minute chart?

[@MarkUrsell]

Hi Connor, good comment. The principle is exactly the same for any timeframe - but if you are using it on the 1 or 5 minute charts you might want to experiment with a slightly wider bandwidth if you are trying to time reversals.

[@antekone1]

Very useful, thanks

[@xs6819]

Yeah but can we make the actual lines of the bollinger bands on exsiting price chart data?

[@IknowIamafool]

Thanks

[@johnbebb7833]

Hi Mark - QUESTION; - you show to take the average of cells F4 thru F23 (20 cells) to initially calculate the average. Then you state to take the entire population of the SAME period... yet you only sample 19 cells? - please explain - thanks.

[@MarkUrsell]

+John Bebb Hi John, you are absolutely correct. The Bollinger Bands should be average and standard deviation over 20 cells.

[@WholeFoodPlantPeople]

thought i was going crazy. glad you saw that too.
looks like his ROW 4 is hiding under the frozen rows.
just a scrolling issue, but how does it affect the math?

[@AlanSeb]

please instead of dragging could you explain how to calculate

[@felixjosephbamba9917]

Thanks Mark ;-)

[@MarkUrsell]

Thanks for commenting Felix.

[@RobertBaindourov]

Can you teach me to be rich? Why does FMCC and FNMA keep dropping at around 4.50 back down to 2.50?

[@billmietelski]

Bill was here.

[@abdelhadiifleh2570]

you made a mistake in the MA you should choose only 11 previous observations not 22