# Formula to count days if a specific condition is met

##### Board Regular

I would like to have a formula or a macro if a formula is not feasible to count the number of days between the moment a position (BUY or SELL) is reversed. Thus between a BUY and SELL or a SELL and a BUY (the 'waiting time' between reversing a position. That means that the transaction (Tx) in the same direction is fine. Thus, if you BUY and then BUY, the count would start from the last time it was bought. If you SELL and then SELL again, when you BUY afterwards, then the count would be between the last SELL and the new BUY. This for each independent symbol.

For example, the table below, when you buy something, it is fine, it can just show blank, however, when you sell it, then to count the number of days that the share was held since the last time it was bought. For example below,
I bought CGX on 10/10/2020 and also on 10/31/2020. BUY and then BUY is fine because it is the same direction, it does not add up the numbers of days. If I SELL CGX on 11/25/2020, then the count should be 25 days (holding period) because it should take the date of the last different direction Tx, BUY on 10/31/2020 and not 10/10/2020. If CGX was SELL again on 11/20/2020 then it is blank because it is the same direction of the last trade (a SELL), the position is not reversed. Then if the same CGX was bought again on 12/20/2020 the count is 11 (the waiting period before doing a transaction in the opposite direction, therefore reversing the position)

I made manually the table below to show the reasoning. and the column COUNT would be the desirable result.
I do not find the best way to do it, if a formula or a macro. In either case, I have doing some tests and without success.

Luis

Tx Symbol Date Count
Sell CGX 11/25/2020 25
Sell CNK 11/16/2020 31
Sell AC 11/18/2020 20
Sell CGX 11/29/2020 blank
Sell VET 11/26/2020 42
Sell CNQ 11/29/2020 27

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Anthony47

##### Well-known Member
A user defined function (udf) is much simpler than a formula, for me.
Put this code into a standard module of your vba project:
VBA Code:
``````Function CkDelay(ByRef myRan As Range) As Variant
Dim myArr, I As Long, cCal As Date, iMax As Long
Dim cTick As String, cOp As String, mySplit, crRep As String
'
crRep = String(myRan.Rows.Count, ",")
mySplit = Split(crRep, ",", , vbTextCompare)
myArr = myRan
For I = UBound(myArr) To 2 Step -1
If myArr(I, 1) <> "" Then
If I > iMax Then iMax = I
cOp = myArr(I, 1)
cTick = myArr(I, 2)
cCal = myArr(I, 3)
For J = I - 1 To 1 Step -1
If myArr(J, 1) = cOp And myArr(J, 2) = cTick Then Exit For
If myArr(J, 1) <> cOp And myArr(J, 2) = cTick Then
mySplit(I - 1) = cCal - myArr(J, 3)
Exit For
End If
Next J
End If
Next I
ReDim Preserve mySplit(0 To iMax - 1)
CkDelay = Application.WorksheetFunction.Transpose(mySplit)
End Function``````
Excel Formula:
``=CkDelay(A2:C20)``
This will return an array with the delays you are looking for.

If your Excel version supports Dynamic arrays then you enter the formula as usual in D2 and the results will be extended up to row 20.

Otherwise you have to enter the formula as an "array formula":
-select D2:D20
-input the formula in the formula bar
-confirm the formula by using the keys Contr+Shift+Enter (not only Enter)

The formula indeed accept an input range longer than the one currently populated, ready for adding new lines; so for example you may use `=CkDelay(A2:C100)`, but the return array will span only to the last used row. You can easily exploit this feature if Dynamic array is supported by your Excel; otherwise you will have the error #N/A in the rows not populated.

Try...

#### jtakw

##### Well-known Member
Hi,

Here's a formula that seems to do what you want:

Book3.xlsx
ABCD
1TxSymbolDateCount
10SellCGX11/25/202025
12SellCNK11/16/202031
13SellAC11/18/202020
14SellCGX11/29/2020
16SellVET11/26/202042
17SellCNQ11/29/202027
Sheet850
Cell Formulas
RangeFormula
D2:D19D2=IFERROR(IF(LOOKUP(2,1/(B\$1:B1=B2),A\$1:A1)=A2,"",C2-LOOKUP(9^9,1/(B\$1:B1=B2),C\$1:C1)),"")

#### jtakw

##### Well-known Member
Just realized, while testing the formula posted above, I forgot to change the 9^9 to 2, formula works either way, just a tad shorter with the 2:

Book3.xlsx
D
2
Sheet850
Cell Formulas
RangeFormula
D2D2=IFERROR(IF(LOOKUP(2,1/(B\$1:B1=B2),A\$1:A1)=A2,"",C2-LOOKUP(2,1/(B\$1:B1=B2),C\$1:C1)),"")

##### Board Regular

Thanks a lot, it works!

How can I add another little complication? for instance an additional column E with the name of the trader.
Thus, the formula would count only the BUY/SELL for the same person. The formula would do the same that you accomplished but only counting for each person,

Thanks,

Luis

#### jtakw

##### Well-known Member
Can you show samples with desired results?

##### Board Regular

Can you show samples with desired results?
Yes, Thanks. See below same table but adding Person names: Person 1, Person 2, Person 3.
The extra step would be that the count counts only when the same Person is buying and selling (or viceversa) the share. If it is other person, then it does not take it into consideration:

#### jtakw

##### Well-known Member
I think you have incorrect Result/Count in your sample data:

Book3.xlsx
ABCDEF
1NameTxSymbolDateCountYou posted
10Person 2SellCGX11/25/2020 Blank
12Person 2SellCNK11/16/20203131
13Person 2SellAC11/18/2020 Blank
14Person 1SellCGX11/29/202050Blank
16Person 1SellVET11/26/20204242
17Person 2SellCNQ11/29/202044Blank
Sheet850
Cell Formulas
RangeFormula
E2:E19E2=IFERROR(IF(LOOKUP(2,1/((C\$1:C1=C2)*(A\$1:A1=A2)),B\$1:B1)=B2,"",D2-LOOKUP(2,1/((C\$1:C1=C2)*(A\$1:A1=A2)),D\$1:D1)),"")

##### Board Regular
Hello,

You are completely right! thanks a lot! it works!

That is a too advance formula to me, I do not even understand it. How can I learn what you did? it seems vectors? or how do you call them?

Thanks,

Luis

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,418
Messages
5,837,129
Members
430,477
Latest member
roseinsydney

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back