Re: Can I Make This Computation Shorter?

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