Thread
:
Can I Make This Computation Shorter?
View Single Post
#
14
Posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
Posts: 2,345
Can I Make This Computation Shorter?
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
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann