Home >> Support Board

Support Board

Date/Time: Fri, 03 May 2024 21:15:32 +0000

How to Post a Message for Support

Spreadshet EMA Custom Calculation fails

View Count: 1082

[2019-09-19 03:02:08] #1 [Go To First Post]
User403758 - Posts: 25
Hello! I tried to get familar with the spreadsheet study. So I made some formulas in column K - ... (SMA, etc,) But I have no idea how to make the correct formula for EMA. I need the EMA in the formula columns because I want add the spreadsheet study to other study collection, so I don't want to use EMA as single study, I need EMA as formula. What I find in your documentation is this: Spreadsheet Example Formulas and Usage: Common Formulas and Usage But the presented formula suggests to use "P4", a cell which is empty by definition as I understand. So I get a long listing of NaN error. Can you help to implement the EMA via formula in the spreadsheet columns K - ...? Thanks!

[2019-09-19 03:53:52] #2 [Go To First Post]
Sawtooth - Posts: 3993
Change all the P4s to K4s: =(E3 - K4) * (2 / (1 + $H$2)) + K4 where H2 is the Length.

[2019-09-19 11:05:22] #3 [Go To First Post]
User403758 - Posts: 25
Hello tomgilb. Many thanks, it worked when I put the formula in cell K3. But why does this not work if I put the formula as before in another formula field? - I only get the correct result in K. All other columns make NaN error. Hm...

[2019-09-19 13:06:24] #4 [Go To First Post]
Sawtooth - Posts: 3993
It will work in any Formula Column's row 3. You just need to edit both column references to the column where the formula resides.

[2019-09-20 01:21:10] #5 [Go To First Post]
User403758 - Posts: 25
Hey, thank you very much! :) Last question, which make me struggeling. My custom SMA has a length of 100 => SMA(100) It averages another formula. If there are only lets say 50 data sets (quotes/lines) availaible the SMA averages < 100 lines. My envelope spreads because of that extrem at the left end of the chart. In Excel you havent this problem. Is it possible to catch/stop a SMA calculation in a spreadsheet if the number of quotes < length of the MA?

[2019-09-20 04:36:51] #6 [Go To First Post]
Sawtooth - Posts: 3993
Is it possible to catch/stop a SMA calculation in a spreadsheet if the number of quotes < length of the MA? Here's the formula for an SMA Skip Zeros. Maybe this would do what you want. Or maybe you could modify this moving average study to start averaging dynamically: https://www.sawtoothtrade.com/free-stuff-18.html

To post a message in this thread, you need to log in with your Sierra Chart account: