Calculate Average True Range in Excel

Hello Experts,

I'm trying to compute the Average True Range for a range of data. I have attached an example of how it is calculate in attachment cs-atr.xlsm. I was hoping if someone could help reproduce the same calculation for me and place the formula in my attachment myatr in cell B2.

| usually provide a long detailed explanation of what I'm trying to achieve, however because the attachment shows exactly what I would like I thought it would be fairly straight forward for someone experienced in Excel. However, please let me know if you need further clarification.

So, in cell K18 there is a ATR value of 0.56. I would very much like to have the formula that was used to get the ATR of 0.56 in cs-atr.xlsm in myatr cell B2.

Cheers

Carlton
cs-atr.xlsm
myatr.xlsm
cpatte7372Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nsonbatyManager IT Service DeskCommented:
you should thing of translating row to column, then get the TR in new column just like the data in myatr.xlsm
0
nsonbatyManager IT Service DeskCommented:
you should think of translating row to column, then get the TR in new column just like the data in myatr.xlsm
0
cpatte7372Author Commented:
nsonbaty

Thanks for responding. The reason why I have to have it in each row is because I will be looking at 500 symbols.

At the moment you see IBM, however when I'm trading I will be looking at ll the stocks on the NASDAQ, S&P and the DOW JONES.

If I could figure I a better way of presenting the data I would - I'm open to suggestions.

Cheers

Carlton
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

krishnakrkcCommented:
Hi,

Try this UDF.

Kris
Function AVGTR(ByRef InputRange As Range) As Single
Dim i As Long, AVG() As Single, n As Long

Data = InputRange

For i = 1 To UBound(Data, 2) Step 3
    n = n + 1
    ReDim Preserve AVG(1 To n)
    If i = 1 Then
        AVG(n) = CSng(Data(1, i) - Data(1, i + 1))
    Else
        AVG(n) = CSng(Application.Max(Data(1, i) - Data(1, i + 1), _
                Abs(Data(1, i) - Data(1, i - 1)), Abs(Data(1, i + 1) - Data(1, i - 1))))
    End If
Next
AVGTR = CSng(Application.Average(AVG))
End Function

Open in new window

0
cpatte7372Author Commented:
Krish,

Thanks mate.

I haven't had a chance to check it out. Going to Mothers Day dinner. Will check it out later this afternoon.

Cheers
0
Patrick MatthewsCommented:
cpatte7372,

No need for a UDF.  This array formula seems to be working for me, and it automatically adjusts to more data being added to the right:

{=3*SUM(CHOOSE(MOD(COLUMN(C2:XFD2),3)+1,C2:XFD2,0,-C2:XFD2))/COUNT(C2:XFD2)}

To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  Excel will then display those braces to indicate that it's an array formula.

Patrick
0
krishnakrkcCommented:
Hi Patrick,

I get 0.95 as opposed to 2.39 (using my UDF). 2.39 is the ATR when I tried the way he illustrated in the attachment.

Kris
0
Patrick MatthewsCommented:
Kris,

Go to the myatr.xlsm file, and use the data sample provided there.  My array formula returns the correct result for it.

Patrick
0
cpatte7372Author Commented:
Hi Matthew,

2.39 is the correct figure.

You guys are great....

Cheers
0
cpatte7372Author Commented:
So Matthew,

I entered the formula in cell B2 and I got the number 478.67. How did you get 2.39?

Cheers
0
cpatte7372Author Commented:
OK Matthew,

So  I hit Ctrl Shift + Enter and I get the number 0.95 as opposed to 2.39
0
cpatte7372Author Commented:
Hi Krish,

I entered the code into the spread but I couldn't get it to work. I quite a novice to programming, can you tell me where I need to enter the code? I tried placing the code into a module but still couldn't get it to work.

Matthew, I would be very interested to learn how you got your formula to work.

Cheers
0
krishnakrkcCommented:
Hi,

Hit Alt + F11 > Insert > Module and paste the there. Hit Alt + Q (to close the VBE window)

Now In B2:

=AVGTR(C2:AR2)

PFA.

Kris
myatr.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cpatte7372Author Commented:
Krish,

Absolutely brilliant mate.....

Cheers
0
Patrick MatthewsCommented:
cpatte7372,

On reflection, I am no longer confident that my formula is correct.  I negelected to notice that your approach is considering not only the current day's high and low, but also the spread between the prior day's close the current day's high and low, and so I did not allow for those in my formula.

If Kris's function is producing the correct output, then I suggest that you accept it as the answer.

:)

Patrick
0
cpatte7372Author Commented:
Brill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.