pankajgrover
Board Regular
- Joined
- Oct 27, 2022
- Messages
- 157
- Office Version
- 2021
- Platform
- Windows
Here below is the formula i am using which works on excel 365 only, but also i need to work on excel 2021...
I think HSTACK function creating issue in 2021 excel, Kindly make it alternative formula ... Thanks...
I think HSTACK function creating issue in 2021 excel, Kindly make it alternative formula ... Thanks...
Excel Formula:
=FILTER(HSTACK((INT(Table1[Date])),REPLACE(Table1[Stocks],1,4,""),Table1[[Qty]:[Price]]),(NOT(ISERROR(SEARCH(B2,Table1[Stocks])&(SEARCH(C2,(INT(Table1[Date])))&(SEARCH(D2,Table1[Buy/Sell]))))))*(INT(Table1[Date])>=IF(E2="",1/1/1900,E2))*(INT(Table1[Date])<=IF(H2="",TODAY(),H2))*((Table1[Price])<=IF(I2="","",I2))*((Table1[Price])>=IF(J2="",1,J2))*(Table1[Qty]<>""))| Shares Search Filter1.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | H | I | J | |||||
| 1 | Particular | Date | Buy/Sell | From Date | To Date | Price<= | Price<= | |||||
| 2 | SEARCH | |||||||||||
| 3 | ||||||||||||
| 4 | 220 | AVG | 1926.87 | |||||||||
| 5 | Date | Particular | Qty | Buy/Sell | Price | Balance Qty | ||||||
| 6 | 06-08-2024 | MICROSOFT | 25 | BUY | 135.70 | 56 | ||||||
| 7 | 06-08-2024 | SAMSUNG | 5 | BUY | 643.50 | 11 | ||||||
| 8 | 06-08-2024 | NETFLIX | 11 | BUY | 3347.70 | 62 | ||||||
| 9 | 06-08-2024 | APPLE | 50 | BUY | 287.00 | 79 | ||||||
| 10 | 06-08-2024 | MICROSOFT | 5 | SELL | 49899.90 | 56 | ||||||
| 11 | 06-08-2024 | SAMSUNG | 1 | BUY | 5187.70 | 11 | ||||||
| 12 | 06-08-2024 | NETFLIX | 25 | BUY | 135.84 | 62 | ||||||
| 13 | 06-08-2024 | APPLE | 25 | BUY | 287.80 | 79 | ||||||
| 14 | 06-08-2024 | MICROSOFT | 11 | BUY | 1610.65 | 56 | ||||||
| 15 | 06-08-2024 | SAMSUNG | 4 | BUY | 2651.60 | 11 | ||||||
| 16 | 06-08-2024 | NETFLIX | 1 | BUY | 2650.75 | 62 | ||||||
| 17 | 06-08-2024 | APPLE | 1 | SELL | 475.50 | 79 | ||||||
| 18 | 05-08-2024 | MICROSOFT | 25 | BUY | 649.30 | 56 | ||||||
| 19 | 05-08-2024 | SAMSUNG | 1 | BUY | 476.85 | 11 | ||||||
| 20 | 05-08-2024 | NETFLIX | 25 | BUY | 800.30 | 62 | ||||||
| 21 | 04-08-2024 | APPLE | 5 | BUY | 6536.35 | 79 | ||||||
| 22 | ||||||||||||
Search | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C4 | C4 | =SUBTOTAL(9,C6:C1000) |
| E4 | E4 | =F4/C4 |
| A6:E21 | A6 | =FILTER(HSTACK((INT(Table1[Date])),REPLACE(Table1[Stocks],1,4,""),Table1[[Qty]:[Price]]),(NOT(ISERROR(SEARCH(B2,Table1[Stocks])&(SEARCH(C2,(INT(Table1[Date])))&(SEARCH(D2,Table1[Buy/Sell]))))))*(INT(Table1[Date])>=IF(E2="",1/1/1900,E2))*(INT(Table1[Date])<=IF(H2="",TODAY(),H2))*((Table1[Price])<=IF(I2="","",I2))*((Table1[Price])>=IF(J2="",1,J2))*(Table1[Qty]<>"")) |
| H6:H22 | H6 | =SUMIFS(Search!$C$6:$C$1000,Search!$B$6:$B$1000,B6,Search!$D$6:$D$1000,"BUY",Search!$G$6:$G$1000,1) - SUMIFS(Search!$C$6:$C$1000,Search!$B$6:$B$1000,B6,Search!$D$6:$D$1000,"SELL",Search!$G$6:$G$1000,1) |
| Dynamic array formulas. | ||
