Ah yes, I see what you mean. All the individual returns are correct until
the maximum value then they are wrong from then on. Now that has got me
puzzled. I look forward to your analysis of what is wrong and how to fix
it.

As a side minor point Do you really need A5 & A6 to be Alsolute references?

--
Regards,

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


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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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


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


"Steve" wrote in message
...
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)" wrote in
message ...
I think this formula will give you the summation directly...

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

Rick


"Steve" wrote in message
...
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