Re: Can I Make This Computation Shorter?

I have to thank you for the attempted rescue effort, but I believe you are being fooled by the sample data you used in the same way I was. My formula worked fine for the sample data I used initially and so I posted my formula. However, if you move the maximum value in column A down the list (say in A50), I think you will see your formula stops working correctly in the same way mine did. Sorry, but I just tried it and that does seem to be the case.

Rick


"Sandy Mann" <sandymann2@xxxxxxxxxxxxxx> wrote in message news:u09ajHHGIHA.936@xxxxxxxxxxxxxxxxxxxxxxx
Steve,

Rick's formula returns the same result for me but only if the whole range is filled in. I assume that you are copying your formula down as you enter data, otherwise your formula returns the value 1 which will distort you count.

If I may suggest a modification to Rick's excellent formula:

=SUMPRODUCT((A7:OFFSET(A7,COUNT(A7:A57)-1,)/(MAX($A$5:$A$6,A7:OFFSET(A7,COUNT(A7:A57),)-1))-1)^2)

works for me proviced that there are no gaps in nthe data

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@xxxxxxxxxxxxxx
Replace @mailinator.com with @tiscali.co.uk


"Steve" <me@xxxxxxxxxxxxxxxxxxxxx> wrote in message news:V_Gdndp7Aft4-7_anZ2dnUVZ8qaqnZ2d@xxxxxxxxxxxx
Rick,

Thanks for the reply, although this doesn't give the same answer as I get going the long way.

Is this part of the expression (MAX($A$5:$A$6,A7:A20)) correct?

Steve




"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in message news:uFjzS2AGIHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
I think this formula will give you the summation directly...

=SUMPRODUCT((A7:A20/(MAX($A$5:$A$6,A7:A20))-1)^2)

Rick


"Steve" <me@xxxxxxxxxxxxxxxxxxxxx> wrote in message news:4fmdnXYefud11b_anZ2dnUVZ8vudnZ2d@xxxxxxxxxxxx
I have a column (A) of stock prices. In the next column I copy down the formula:

=(A7/(MAX($A$5:A7))-1)^2
all the way to
=(A57/(MAX($A$5:A57))-1)^2

I then sum the new column (B) of values before further computations (you may recognize this as "Drawdown squared" leading to the "Ulcer Index").

My question is: Can I miss out column B and go directly from the list of stock prices to the summation of the values for drawdown squared? I tried to make this into some sort of array, without success.

Steve










.

Loading