Forum Discussion
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 |
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)
