Biff,

You are correct, my original expression checks for the MAX value for all the
preceeding values as it moves down the column (thus increasing the range
each row).

So, is it not possible to replicate my result without the intermediate
column of data?


Steve


"T. Valko" wrote in message
...
Is this part of the expression (MAX($A$5:$A$6,A7:A20)) correct?

It's the same as - MAX(A5:A20)

Based on how your original formula is written, this won't work.

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

Your original formula is evaluating a (potentially) different MAX value
every cell it's copied down.

=(A7/(MAX($A$5:A7))-1)^2

Whereas, the SUMPRODUCT formula is using the *same* max value throughout.


--
Biff
Microsoft Excel MVP


"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:

=SUMPRODUCT((A7:A20/(MAX($A$5:$A$6,A7:A20))-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