crossing rate?

[vikramsj85]

I wanted to know how to create a level crossing rate formula in excel.

For instance if I was given the time series

0.2%, 0.7%, 0.3%, 0.6%, 0.7%, 0.1%, 0.2%, 0.4%, 0.9%

I want to be able to determine how many times the time series has crossed the 0.5% mark - which in this case would be 5 times

is there a simple way to do this?

 

[anandvarma]

try this

Excel Workbook
	A	B
1	0.20%	4
2	0.70%
3	0.30%
4	0.60%
5	0.70%
6	0.10%
7	0.20%
8	0.40%
9	0.90%
Sheet1

 

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT(((0.005-A1:A99)*(0.005-A2:A100)<0)*(A2:A100<>""))

Note that the two ranges have the same number of cells, but are offset from each other by one row (the range with the higher maximum row in it is the one that must be tested for not equalling the empty string ""). The maximum row numbers in the range can be larger than the number of percentages in your data (so the above formula, as written, would work for your numbers in A1 to A10).

 

[vikramsj85]

anadvarma -

COUNTIF(A1:A9,">="&0.5%) doesnt seem to work. only gives me the number of times thats below 0.5% not the number of times it crosses the horizontal line of 0.5%.


Rick, will give your formula a try. Thanks

 

[vikramsj85]

Give this formula a try...

=SUMPRODUCT(((0.005-A1:A99)*(0.005-A2:A100)<0)*(A2:A100<>""))

Note that the two ranges have the same number of cells, but are offset from each other by one row (the range with the higher maximum row in it is the one that must be tested for not equalling the empty string ""). The maximum row numbers in the range can be larger than the number of percentages in your data (so the above formula, as written, would work for your numbers in A1 to A10).

Gave this a shot but didnt seem to work.

What im basically looking to do is determine a quick algorithim to calculate the maximum value that the time series crosses the most.

In this case it crosses 0.5% 5x, and 0.3% 6x...thus the function should return 0.3% and not 0.5% (might not be the actual max, just explaining how it should work).

 

[Rick Rothstein]

What im basically looking to do is determine a quick algorithim to calculate the maximum value that the time series crosses the most.

In this case it crosses 0.5% 5x, and 0.3% 6x...thus the function should return 0.3% and not 0.5% (might not be the actual max, just explaining how it should work).

That is not what your asked for originally (not even close). What is the range of values your data covers? And what range of cells is your data in? Also, did you want to look at each possible number in the range looking for the one crossed the most? I haven't thought about it much yet, but my gut is telling me a VB solution might be required... is a VB solution acceptable?

 

[vikramsj85]

That is not what your asked for originally (not even close). What is the range of values your data covers? And what range of cells is your data in? Also, did you want to look at each possible number in the range looking for the one crossed the most? I haven't thought about it much yet, but my gut is telling me a VB solution might be required... is a VB solution acceptable?

range of data: 0.0%-2.0%

range of cells: varies, but for argument say its C1:C40

VB solution is totally fine, would rather have a UserDefinedFunc. and not a Macro if thats doable. Had a few tries at coding it but to no avail.

 

[Rick Rothstein]

I think this UDF will do what you asked for (if not, let me know in what way it doesn't)...

Function MaxCrossover(Rng As Range) As Double
  Dim X As Double, V As Double, Crossover As Double
  For X = 0.001 To 0.02 Step 0.001
    V = Evaluate("=SUMPRODUCT(((" & X & "-" & Rng.Address & ")*(" & X & "-" & Rng.Offset(1).Address & ")<0)*(" & Rng.Offset(1).Address & "<> """"))")
    If V > Crossover Then
      MaxCrossover = X
      Crossover = V
    End If
  Next
End Function

Feed the range you want to test over as an argument to the function (that would be C1:C40 as per your last message).

 

[vikramsj85]

I think this UDF will do what you asked for (if not, let me know in what way it doesn't)...

Function MaxCrossover(Rng As Range) As Double
  Dim X As Double, V As Double, Crossover As Double
  For X = 0.001 To 0.02 Step 0.001
    V = Evaluate("=SUMPRODUCT(((" & X & "-" & Rng.Address & ")*(" & X & "-" & Rng.Offset(1).Address & ")<0)*(" & Rng.Offset(1).Address & "<> """"))")
    If V > Crossover Then
      MaxCrossover = X
      Crossover = V
    End If
  Next
End Function

Feed the range you want to test over as an argument to the function (that would be C1:C40 as per your last message).

Looks like that did the trick! THANK YOU RICK.

Would you mind explaining how the SUMPRODUCT line in the code you provided is being used?