Compare a cell value with all the following cell values

[ibmy]

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.

 

[FormR]

Hi, here's one option you can try:

Sub m()
Dim v As Variant, i As Long, u As Long, low As Double
v = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
u = UBound(v)
low = v(u, 1)
v(u, 1) = ""
For i = u - 1 To 1 Step -1
    If v(i, 1) <= low Then
        low = v(i, 1)
        v(i, 1) = "Low"
    Else
        v(i, 1) = ""
    End If
Next i
Range("F2").Resize(u).Value = v
End Sub

 

[ibmy]

Thank you Master @FormR
The code take a second to produce the result for 250k+ row data.

How can the code produce the result faster than the formula I apply before?

 

[FormR]

How can the code produce the result faster than the formula I apply before?

Hi, it's the MIN() function that's probably killing it, the last cell that formula is in needs to check 250,000 cells, the next one up 249,999 the next one 249,998, the next 249,997 - that's already nearly a million cell reads are we're only 4 formulas into the 250k you have!

 

[FormR]

If you really wanted a formula, you could try this that utilises a helper column.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1		Offer			Helper	Low
2		1.3			0.2
3		1.7			0.2
4		1.6			0.2
5		2.4			0.2
6		0.9			0.2
7		0.3			0.2
8		0.8			0.2
9		0.2			0.2	Low
10		0.2			0.2	Low
11		1.8			1
12		1.4			1
13		3			1
14		1			1	Low
15		1.3			1.1
16		1.1			1.1	Low
17		1.9			1.7
18		1.7			1.7	Low
19		2			1.7
20		2.1			1.7
21		2.3			1.7
22		1.7			1.7	Low
23		1.8			1.8	Low
24		1.9			1.9	Low
25		2.5			0
Sheet1

Cell Formulas
Range	Formula
E2:E25	E2	=IF(B2<IF(E3=0,B3,E3),B2,E3)
F2:F25	F2	=IF(B2=E2,"Low","")

 

[ibmy]

Thanks Master @FormR
I really appreciate your help.