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

I hope the table below makes the calculation clearer. This is the simplest
form but with the most columns.
Here are the column formulae:

Prices = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2
Column D is then summed at the bottom

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120

Sum DD^2 1484


After this I realized I could go directly from the prices column to the
Ddown^2 column using the formula:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve










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

Try Again!

I hope the table below makes the calculation clearer. This is the simplest
form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3
54
148 166 -10.9
120

Sum DD^2
1484


After this I realized I could go directly from the prices column to the DD^2
column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

As Biff intimated, MAX returns only one value. The best that I can come up
with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the simplest
form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2
740
130 132 -1.0
1
166 166 0.0
0
154 166 -7.3 54
148 166 -10.9 120

Sum
DD^2 1484


After this I realized I could go directly from the prices column to the
DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

Sandy,

I've never used a UDF before. However, I copied your code into a new module
in VBA. It appeared as it should when I hit the paste function button and
selected it.

However, the window which opens says: "This function takes no arguments".

If I try to insery a range of cells into =SquareIt() , then I get #VALUE!
returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can come
up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0
0
122 132 -7.1
51
108 132 -17.7
314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120

Sum
DD^2 1484


After this I realized I could go directly from the prices column to the
DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can come
up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C
D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3
206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120

Sum
DD^2 1484


After this I realized I could go directly from the prices column to the
DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as =SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can come
up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0
0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120

Sum
DD^2 1484


After this I realized I could go directly from the prices column to the
DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate sum
DD^2.

Steve

The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the variable n
to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you to
see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0
0
124 124 0.0
0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120

Sum
DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve

Thnking about it some more, you don't need to check for the lasr data row:

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double


For x = 3 To Rows.Count
If Cells(x, 1).Value = "" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function

--
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
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the variable
n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve

Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

"Sandy Mann" wrote in message
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the variable
n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve

Glad that you got it working. You can still leave the title in that cell,
just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

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
...
Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

"Sandy Mann" wrote in message
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve

Sandy,

At the risk of really pi$$ing you off, one further question.

You've solved the problem for a column of prices in column A. I can also
adjust your UDF for another column, say, column D.

But, what if I have 50 columns of prices (for 50 different stocks). I now
want to use your UDF at the head of each column to calculate =SquareIt2()
for that particular column.

It seems I need to replace 'cells(x,1)' with some relative referrence that
says "start 3 rows down in the same column" or some such.

Any thoughts?

Steve



"Sandy Mann" wrote in message
...
Glad that you got it working. You can still leave the title in that cell,
just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

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
...
Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

"Sandy Mann" wrote in message
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly calculate
sum DD^2.

Steve

Now you need to include an argument in the parenthesis:

Function SquareIt3(Here As Long)
Application.Volatile

Dim x As Long
Dim Temp As Double


For x = 3 To Rows.Count
If Cells(x, Here).Value = "" Then Exit For
Temp = Temp + (100 * (Cells(x, Here).Value _
/ (Application.Max( _
Range(Cells(2, Here), Cells(x, Here)))) _
- 1)) ^ 2
Next x

SquareIt3 = Temp

End Function

And enter the function as:

=SquareIt3(Column())

This assumes that the data starts in Row 2, if it starts in another Row
change the 3 in:
For x = 3 To Rows.Count
to one more than the start of data Row and the 2 in:
Range(Cells(2, Here), Cells(x, Here)))) _
to the same as the start of data Row


--
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
...
Sandy,

At the risk of really pi$$ing you off, one further question.

You've solved the problem for a column of prices in column A. I can also
adjust your UDF for another column, say, column D.

But, what if I have 50 columns of prices (for 50 different stocks). I now
want to use your UDF at the head of each column to calculate =SquareIt2()
for that particular column.

It seems I need to replace 'cells(x,1)' with some relative referrence that
says "start 3 rows down in the same column" or some such.

Any thoughts?

Steve



"Sandy Mann" wrote in message
...
Glad that you got it working. You can still leave the title in that
cell, just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

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
...
Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

"Sandy Mann" wrote in message
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an
empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that it
found above.

Post back or send me an e-mail and I will send a sample workbook for
you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a new
module in VBA. It appeared as it should when I hit the paste
function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column
to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly
calculate
sum DD^2.

Steve

Sandy,

That's perfect. (and I've learned a lot from your explanations...)

Many thanks,

Steve


"Sandy Mann" wrote in message
...
Now you need to include an argument in the parenthesis:

Function SquareIt3(Here As Long)
Application.Volatile

Dim x As Long
Dim Temp As Double


For x = 3 To Rows.Count
If Cells(x, Here).Value = "" Then Exit For
Temp = Temp + (100 * (Cells(x, Here).Value _
/ (Application.Max( _
Range(Cells(2, Here), Cells(x, Here)))) _
- 1)) ^ 2
Next x

SquareIt3 = Temp

End Function

And enter the function as:

=SquareIt3(Column())

This assumes that the data starts in Row 2, if it starts in another Row
change the 3 in:
For x = 3 To Rows.Count
to one more than the start of data Row and the 2 in:
Range(Cells(2, Here), Cells(x, Here)))) _
to the same as the start of data Row


--
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
...
Sandy,

At the risk of really pi$$ing you off, one further question.

You've solved the problem for a column of prices in column A. I can also
adjust your UDF for another column, say, column D.

But, what if I have 50 columns of prices (for 50 different stocks). I now
want to use your UDF at the head of each column to calculate =SquareIt2()
for that particular column.

It seems I need to replace 'cells(x,1)' with some relative referrence
that says "start 3 rows down in the same column" or some such.

Any thoughts?

Steve



"Sandy Mann" wrote in message
...
Glad that you got it working. You can still leave the title in that
cell, just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

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
...
Sandy,

It works!

The problem was that I didn't have a blank cell under the last price in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

"Sandy Mann" wrote in message
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an
empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that
it
found above.

Post back or send me an e-mail and I will send a sample workbook for
you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a
new
module in VBA. It appeared as it should when I hit the paste
function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I
get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I
can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column
to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly
calculate
sum DD^2.

Steve

You're very welcome, I think that we both learned.

--
Regards,

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
...
Sandy,

That's perfect. (and I've learned a lot from your explanations...)

Many thanks,

Steve


"Sandy Mann" wrote in message
...
Now you need to include an argument in the parenthesis:

Function SquareIt3(Here As Long)
Application.Volatile

Dim x As Long
Dim Temp As Double


For x = 3 To Rows.Count
If Cells(x, Here).Value = "" Then Exit For
Temp = Temp + (100 * (Cells(x, Here).Value _
/ (Application.Max( _
Range(Cells(2, Here), Cells(x, Here)))) _
- 1)) ^ 2
Next x

SquareIt3 = Temp

End Function

And enter the function as:

=SquareIt3(Column())

This assumes that the data starts in Row 2, if it starts in another Row
change the 3 in:
For x = 3 To Rows.Count
to one more than the start of data Row and the 2 in:
Range(Cells(2, Here), Cells(x, Here)))) _
to the same as the start of data Row


--
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
...
Sandy,

At the risk of really pi$$ing you off, one further question.

You've solved the problem for a column of prices in column A. I can also
adjust your UDF for another column, say, column D.

But, what if I have 50 columns of prices (for 50 different stocks). I
now want to use your UDF at the head of each column to calculate
=SquareIt2() for that particular column.

It seems I need to replace 'cells(x,1)' with some relative referrence
that says "start 3 rows down in the same column" or some such.

Any thoughts?

Steve



"Sandy Mann" wrote in message
...
Glad that you got it working. You can still leave the title in that
cell, just test for it in the cell. For instance if it did say
"Summation of Ddown^2"
(without the quotes) then change the UDF to

Function SquareIt2()
Application.Volatile

Dim x As Long
Dim Temp As Double

For x = 3 To Rows.Count
If Cells(x, 1).Value = "Summation of Ddown^2" Then Exit For
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt2 = Temp

End Function




--
Regards,

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
...
Sandy,

It works!

The problem was that I didn't have a blank cell under the last price
in
column A (I had the title of the summation cell...).

Many thanks for your help with this.

Steve

"Sandy Mann" wrote in message
...
The rage is defined in the code:

For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

goes down Column A, [ the 1 in the Cells(x,1) ], until it finds an
empty
cell then it copies the value of the counting variable x into the
variable n to preserve it.

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

this again goes down Column A but now only until the Row number that
it
found above.

Post back or send me an e-mail and I will send a sample workbook for
you
to see.


--
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
...
Sandy,

Excel 2000 here.

You say: It works for me in XL97 without a range, calling it as
=SquareIt()
How do you tell it what the range is?
Assuming I have a column of prices in colA, how would I use your
UDF?

Steve

"Sandy Mann" wrote in message
...
Mmmmm.....

It works for me in XL97 without a range, calling it as =SquareIt(),
or with a range if I change the Function tittle to

Function SquareIt(R As Range)

and calling it as =SquareIT(A2:A1000) or even =SquareIT(A1)

even although I don't use the range in the code

Are you by any chance using XL2007?


--
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
...
Sandy,

I've never used a UDF before. However, I copied your code into a
new
module in VBA. It appeared as it should when I hit the paste
function
button and selected it.

However, the window which opens says: "This function takes no
arguments".

If I try to insery a range of cells into =SquareIt() , then I
get
#VALUE! returned.

I assume I'm doing something wrong?

Steve






"Sandy Mann" wrote in message
...
As Biff intimated, MAX returns only one value. The best that I
can
come up with is a UDF:

Option Explicit
Function SquareIt()
Application.Volatile
Dim x As Long
Dim n As Long
Dim Temp As Double
For x = 2 To 1000
If Cells(x, 1).Value = "" Then
n = x - 1
Exit For
End If
Next x

For x = 3 To n
Temp = Temp + (100 * (Cells(x, 1).Value _
/ (Application.Max( _
Range(Cells(2, 1), Cells(x, 1)))) _
- 1)) ^ 2
Next x

SquareIt = Temp

End Function

Does that do what youm want?

--
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
...
Try Again!

I hope the table below makes the calculation clearer. This is
the
simplest form but with the miost columns.
Here are the column formulae:

Price = stock price
Peak = IF(A3B2,A3,B2)
Drawdown % = 100*(A3/B3-1)
Ddown^2 = C3^2

A B C D
Prices Peak Drawdown % Ddown^2

118 118 0.0 0
124 124 0.0 0
129 129 0.0 0
110 129 -14.3 206
132 132 0.0 0
122 132 -7.1 51
108 132 -17.7 314
96 132 -27.2 740
130 132 -1.0 1
166 166 0.0 0
154 166 -7.3 54
148 166 -10.9 120


Sum DD^2 1484


After this I realized I could go directly from the prices column
to
the DD^2 column using:

=10000*(A3/(MAX($A$3:A3))-1)^2

Hence my question, can I go one step further and directly
calculate
sum DD^2.

Steve