Match conditions in unspecified sequence of cells in column using formula instead of VBA?

[adorr]

Can any Excel wizards here think of a way to use a formula (or perhaps several) to look down through a column of data and match for conditions that *change* according to what values are in those cells?

Specifically, I'm hoping to create formula for a trailing stop. If the starting value in A1 is 100 and there are hundreds of rows of data, then the formula (in B1) would look down through those rows sequentially. If the value 95 (initial threshold) appears before the value 110, then the formula returns 95 in B1. BUT, if the the value 110 appears then the initial threshold is reset to a higher value (say, 105), and so now the formula continues down the rows but it won't stop until it hits a cell with 105. If the value in the cells rises to 111, then the threshold is raised to 106, etc, so that the "stop" from then on always trails 5 points behind the max.

I could do this in VBA, but it would be slow because I'd like to have these formulas in every cell in the B column and I have tens of thousands of rows of data.

Thanks in advance for your help!

 

[RickXL]

Hi,

I don't understand your rules or I would be able to show you a more realistic macro.

However, it illustrates a quick method of calculating using arrays in VBA. Basically, it copies all of columns A and B into a 2D array and you can then put it into a for/next loop to carry out your calculations. (I have just made up something simple but it can be as complicated as you need.) The entire array is then written back to the worksheet with one instruction.

You do hit memory limits eventually but it will usually perform calculations over 100,000 rows in a second or two on my, not very quick, PC.

Sub QuickCalc()

    Dim ws As Worksheet
    Dim arrAB As Variant
    Dim x As Long
    Dim y As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    x = 5
    
    With ws
        arrAB = Intersect(.UsedRange, Columns("A:B"))
        For i = 2 To UBound(arrAB, 1)
            If arrAB(i, 1) > x Then
                arrAB(i, 2) = arrAB(i, 1) * 5
            Else
               arrAB(i, 2) = arrAB(i, 1) * -5
            End If
        Next
        .Range("A1").Resize(UBound(arrAB, 1), 2) = arrAB
    End With

End Sub

 

[adorr]

Thanks for your help! I really don't understand arrays very well, sorry to be dumb. Can you explain what the For-Next loop is doing? If I can understand it, I might be able to modify it to work with my rules.

Alternatively, here are the rules I'm trying to employ in not-quite-code (sorry I wasn't clearer earlier!).

TrailingStop = StartingCellValue-5
Max = StartingCellValue+5

For

If CurrentCellValue <= TrailingStop then FormulaCellValue=CurrentCellValue

Else
If CurrentCellValue > Max then
Set Max=CurrentCellValue
Set TrailingStop=Max-1

Next (i.e. next cell in the array)

End


What this should do is if the value initially falls, a Stop will be triggered. If the value initially rises at least +5, then it will be allowed to keep going as long as it is rising (i.e. set new max). But it will end whenever it stops rising and starts to fall again (i.e. new TrailingStop is Max-1).

 

[RickXL]

Hi again,

I think I have entered your code correctly but it leaves some blanks (depending on the data) because sometimes the value is greater than trail;ingStop but still less than Max.

However, you might be able to work it out when you see your variables in the macro.

I have created an array called arrAB so-called because it is an array of columns A and B. basically, you can treat this like Cells so arrAB(1,2) is like Cells(1,2). You can't go outside the bounds of the array though so you can't say arrAB(1,3) - unless you change something else.

Just pretend you are using Cells to do your calcs. The for/next loop starts at the second row and processes all the rows.

The line with Intersect in it is just one way to read columns A and B into the array.
UBound(arrAB, 1) is a way of getting the number of rows in the array
The final .Range statement write the whole array back to the spreadsheet in one go.
So column A will be overwritten. I expect I could change that if you wanted.
It should not be a problem as long as you never change anything in column 1 of the array (e.g. arrAB(i,1) = something)

Sub QuickCalc()

    Dim ws As Worksheet
    Dim arrAB As Variant
    Dim TrailingStop As Long
    Dim Max As Long
    Dim i As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    With ws
        arrAB = Intersect(.UsedRange, Columns("A:B"))
        TrailingStop = arrAB(2, 1) - 5
        Max = arrAB(2, 1) + 5
        For i = 2 To UBound(arrAB, 1)
            If arrAB(i, 1) <= TrailingStop Then
                arrAB(i, 2) = arrAB(i, 1)
            Else
                If arrAB(i, 1) > Max Then
                    Max = arrAB(i, 1)
                    TrailingStop = Max - 1
                End If
            End If
        Next
        .Range("A1").Resize(UBound(arrAB, 1), 2) = arrAB
    End With

End Sub

 

[adorr]

Thanks!