pls turn this into VB macro

The spreadsheet calculates the Up and Down Capture ratio with input for the fund and benchmark's monthly return, and results are displayed in cells F96 & I96. Because I don't know how to write a macro in VB, can someone please help me to turn this into macro so that I can have the formula like =UpCapture(B:B, C:C)

so, what I want is to have a single cell display the end result with 2 input columns

the definition and formula to calculate up capture and down capture ratio is as follows: (also the method I used in the spreadsheet)
http://support.pertrac2000.com/statbody.asp#upcap

Hi,

Do you mean into a UDF (User Defined Function) ?

Carim

yes, yes.. thanks

Can anybody help please?

Vicky,

Had a look at your spreadsheet ...

Contrary to the reference site you have indicated, your calculations do not reflect the exact definitions of the Upcapture ratio and the Downcapture ratio

Your calculations are adjusted for annualization ...
So my first question would be which version are you after ...?

HTH
Carim

Vicky,

Sorry if I was not clear ...

1. The site describes a formula which is not annualized ...

2. Your spreadsheet uses a formula which is annualized ...

The question is

Which formula would you rather have ...?

HTH
Carim

Ops.. I just edited my previous post.. please see above, sorry, my fault

Since it is not getting clearer, I will include the annualization as an option ...
and, by the way, if you see anything green or orange in the worksheet you have sent ... let me know where exactly ...

Carim

I am wrong and the website's formula is correct and also the way I want to calculate the capture ratio, I have already corrected in the "Up and Down.xls" spreadsheet I have attached recently.

In Row 96-97:

Col G & J are the result for Up & Down Capture Ratios respectively (I marked them in blue)

Col N & Q are the result for Up & Down Percentage Ratio respectively (I marked them in green)

Col T & Q are the result for Up & Down Number Ratio respectively (I marked them in orange)

Overall, I would like to have 3 functions written in VB to avoid all calculations, one function for capture ratio , one for percentage ratio and one for number ratio

thanks

As a working rule, please do not edit previous posts ...
it makes it very difficult to follow the thread ...

As far as the zipped worksheet is concerned, you are definitely talking about another file ...

Bye
Carim

this is the file