ibmy
Board Regular
- Joined
- Mar 4, 2020
- Messages
- 129
- Office Version
- 2016
- Platform
- Windows
Low.xlsb | |||||||
---|---|---|---|---|---|---|---|
B | F | ||||||
1 | Offer | Low | |||||
2 | 1.30 | ||||||
3 | 1.70 | ||||||
4 | 1.60 | ||||||
5 | 2.40 | ||||||
6 | 0.90 | ||||||
7 | 0.30 | ||||||
8 | 0.80 | ||||||
9 | 0.20 | Low | |||||
10 | 0.20 | Low | |||||
11 | 1.80 | ||||||
12 | 1.40 | ||||||
13 | 3.00 | ||||||
14 | 1.00 | Low | |||||
15 | 1.30 | ||||||
16 | 1.10 | Low | |||||
17 | 1.90 | ||||||
18 | 1.70 | Low | |||||
19 | 2.00 | ||||||
20 | 2.10 | ||||||
21 | 2.30 | ||||||
22 | 1.70 | Low | |||||
23 | 1.80 | Low | |||||
24 | 1.90 | Low | |||||
25 | 2.50 | ||||||
Sheet4 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F24 | F2 | =IF(B2<=MIN(B3:B$25),"Low","") |
When there are no value lower than a cell value, assign ("Low"), else ("").
The formula start at row 2 until second last row( last row - 1 ).
Currently, there is no problem with the formula, the formula work fine but it take long time/sometime Excel hang for 250k row data, for data less than 60k it is fine.
Expected to have VBA solution or any other alternative formula.
I think current formula not good at handling "else" part (""). I do not know how the formula work detail in Excel, but I assume, let say :
Current cell value B2 is 1.30, at B6 value is 0.90 which is lower than B2, assign F3 ("") then check/loop next new cell B3 instead of going through
until last row B25 (B$25) / B7-B25.
For "Low" part, the formula work as intended, it need to check all following cell/value until last row to confirm a value is Low.
Last edited: