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