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