Re: Can I Make This Computation Shorter?

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

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


"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in
message news:e%23IDghHGIHA.3548@xxxxxxxxxxxxxxxxxxxxxxx
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