Average True Range - Google Sheets

N

Ndanileka

Well-Known Member
Joined
Aug 9, 2010
Messages
168
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

Werfetter

Senior Member
Joined
Jan 29, 2010
Messages
985
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))& ")"))​
)​
)​
)​
)
 
N

Ndanileka

Well-Known Member
Joined
Aug 9, 2010
Messages
168
Werfetter said:
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))& ")"))​
)​
)​
)​
)
Click to expand...

I have pinned down the error to the 3 numbers in red, values above 20 return the error.
 
You must log in or register to reply here.
Top