asked on

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

nsonbaty

you should thing of translating row to column, then get the TR in new column just like the data in myatr.xlsm

nsonbaty

you should think of translating row to column, then get the TR in new column just like the data in myatr.xlsm

cpatte7372

ASKER

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

krishnakrkc

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

cpatte7372

ASKER

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

Patrick Matthews

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

krishnakrkc

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

Patrick Matthews

Kris,

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

Patrick

cpatte7372

ASKER

Hi Matthew,

2.39 is the correct figure.

You guys are great....

Cheers

cpatte7372

ASKER

So Matthew,

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

Cheers

cpatte7372

ASKER

OK Matthew,

So I hit Ctrl Shift + Enter and I get the number 0.95 as opposed to 2.39

cpatte7372

ASKER

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

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cpatte7372

ASKER

Krish,

Absolutely brilliant mate.....

Cheers

Patrick Matthews

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

cpatte7372

ASKER

Brill