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

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

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

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

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

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

Steve,

Perhaps I did not make it clear but with the Offset() modification the
formula then works the same as your formula and the sum as data is entered.

--
HTH

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


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


"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

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

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