Forum Discussion
anshul1983
Aug 06, 2024Copper Contributor
Formula to view only certain rows that fulfills few conditions
Just want some help on this table as one can see the data in these table. I just want to view only few rows which fulfill few of the conditions.
1) Volume is greater than 5000.
2) Values in row of PE is more than 10%
29-Aug-24 | CE/PE | Options Price | AUGUST24-BID | AUGUST-LTP | AUGUST24-ASK | VOL. | OI |
20000 | CE | 0 | 4082 | 4082 | 4082 | 0 | 0 |
20000 | PE | 0 | -79378.94% | -13577.25% | -79378.94% | 0 | 0 |
20500 | CE | 3551 | 50 | 46 | -16 | 1425 | 166900 |
20500 | PE | 16 | 22.79% | 9.86% | -7.15% | 663900 | 602325 |
21000 | CE | 3074 | 41 | 30 | 6 | 17700 | 525775 |
21000 | PE | 22 | 22.07% | 7.53% | 3.05% | 1503575 | 1917650 |
21500 | CE | 2590 | 37 | 23 | -8 | 2300 | 49975 |
21500 | PE | 31 | 23.61% | 6.90% | -5.22% | 1069275 | 999000 |
22000 | CE | 2111 | 33 | 19 | -5 | 27100 | 463500 |
22000 | PE | 49 | 26.83% | 7.54% | -4.15% | 2255175 | 1699125 |
22500 | CE | 1657 | 22 | 1 | 11 | 12625 | 263175 |
22500 | PE | 77 | 23.79% | 0.79% | 11.62% | 2115400 | 1151275 |
23000 | CE | 1195 | 15 | 15 | 2 | 92775 | 530075 |
23000 | PE | 129 | 23.92% | 12.39% | 3.18% | 3590650 | 3119400 |
23500 | CE | 800 | 13 | 3 | 6 | 271925 | 488375 |
23500 | PE | 221 | 41.53% | 5.11% | 18.62% | 3914625 | 3106800 |
24000 | CE | 459 | 0 | 0 | -2 | 3666225 | 2196250 |
24000 | PE | 378 | -1586.96% | 0.00% | -1586.96% | 8202325 | 4343125 |
24500 | CE | 206 | 4 | 2 | -2 | 4465625 | 3373125 |
24500 | PE | 622 | 11.26% | 6.25% | -5.44% | 2669225 | 3391025 |
25000 | CE | 72 | 9 | 11 | 0 | 5362800 | 3527850 |
25000 | PE | 980 | 14.64% | 17.17% | -0.08% | 748700 | 2051800 |
25500 | CE | 25 | 19 | 4 | 1 | 3299900 | 1888750 |
25500 | PE | 1439 | 21.34% | 4.74% | 0.94% | 133925 | 499000 |
26000 | CE | 13 | 25 | 22 | 7 | 2501075 | 2357175 |
26000 | PE | 1909 | 20.77% | 18.58% | 5.52% | 66500 | 764825 |
26500 | CE | 7 | 32 | 1 | 6 | 638700 | 732650 |
26500 | PE | 2425 | 21.08% | 0.56% | 3.90% | 16800 | 110850 |
27000 | CE | 0 | 2918 | 2918 | 2918 | 0 | 0 |
27000 | PE | 0 | 0.00% | 0.00% | 0.00% | 0 | 0 |
27500 | CE | 0 | 3418 | 3418 | 3418 | 0 | 0 |
27500 | PE | 0 | 0.00% | 0.00% | 0.00% | 0 | 0 |
28000 | CE | 0 | 3918 | 3918 | 3918 | 0 | 0 |
28000 | PE | 0 | 0.00% | 0.00% | 0.00% | 0 | 0 |
- Rodrigo_Steel Contributor
Use filter function
=FILTER(Sheet1!A2:H35;(Sheet1!B2:B35="PE") *(Sheet1!D2:D35 + Sheet1!E2:E35 + Sheet1!F2:F35) >10%; "")sample file > anshul1983.xlsx - Microsoft Excel Online (live.com)