Average True Range - Google Sheets

[Ndanileka]

I copied the below formula from Google Doc help forums, on how to calculate ATR using Google sheets.
The formula works for number of days below 20, for days above 20 it returns the below error:
"Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 22. Actual: 21"

Could you please help me by fixing the error.
Trading has drawn me into google sheets & excel, and at the moment the below is beyond my skills, I'm still working with IF statements.

$B1 - Ticker Cell

40 - Period (days)

=average(ARRAYFORMULA(query(query(transpose(abs(query({query({Googlefinance($B1,"High",today(),today()-(40)),Googlefinance($B1,"low",today(),today()-(40+6))},"select Col1,Col2,Col4 order by Col1 desc limit "&40&" "), query(Googlefinance($B1,"close",today(),today()- (40+6)),"select Col2 order by Col1 desc limit "&40&" offset 1 label Col2 'closeyest' ")}, "select Col2-Col3, Col2-Col4, Col3-Col4"))), "select max(Col"&join(",max(Col",row(indirect("A1:A"&40))&")")))))

 

[Werfetter]

Lol I thought I would give it a shot but there are enough non-standard stuff in there that I have no idea what they would do without the original sheet. That it makes it quite difficult. Some advice though when looking at a formula like the one above it really helps to look at it like a coding function and formatting it like one:

=average(

ARRAYFORMULA(

query(

query(

transpose(

abs(

query(

{query(

{Googlefinance($B1,"High",today(),today ()-(40)),Googlefinance($B1,"low",today(),today()-(40+6))}
,"select Col1,Col2,Col4 order by Col1 desc limit "&40&" "
​

)
, query(Googlefinance($B1,"close",today(),today()- (40+6)),"select Col2 order by Col1 desc limit "&40&" offset 1 label Col2 'closeyest' ")}, "select Col2-Col3, Col2-Col4, Col3-Col4"​

)​

)​

), "select max(Col"&join(",max(Col",row(indirect("A1:A"&40))& ")"))​

)​

)​

)​

)

 

[Ndanileka]

I forgot to link the original forum, this might help.
https://productforums.google.com/forum/#!topic/docs/gENldwEI040;context-place=forum/docs
File Link
https://docs.google.com/spreadsheets/d/1J8zVBqdwTZ_dt-Vc_3olkopnfG9lEYUCd5oqyxvHyn8/edit?usp=sharing

 

[Ndanileka]

Lol I thought I would give it a shot but there are enough non-standard stuff in there that I have no idea what they would do without the original sheet. That it makes it quite difficult. Some advice though when looking at a formula like the one above it really helps to look at it like a coding function and formatting it like one:

=average(

ARRAYFORMULA(

query(

query(

transpose(

abs(

query(

{query(

{Googlefinance($B1,"High",today(),today ()-(40)),Googlefinance($B1,"low",today(),today()-(40+6))}
,"select Col1,Col2,Col4 order by Col1 desc limit "&40&" "
​

)
, query(Googlefinance($B1,"close",today(),today()- (40+6)),"select Col2 order by Col1 desc limit "&40&" offset 1 label Col2 'closeyest' ")}, "select Col2-Col3, Col2-Col4, Col3-Col4"​

)​

)​

), "select max(Col"&join(",max(Col",row(indirect("A1:A"&40))& ")"))​

)​

)​

)​

)

I have pinned down the error to the 3 numbers in red, values above 20 return the error.

 

[Nod]

Googlefinance function docs: https://support.google.com/docs/answer/3093281?hl=en

Using "limit" in the query function: http://blogmines.com/blog/how-to-limit-results-returned-by-query-function-in-google-spreadsheet/
Why is there "&"'s around the limit value?