Using the "usedrange" to insert smallest value in array separated by a word

I have a column with numbers and the word "Null" between these numbers. For a particular array, I want a code which puts in each row of that array the array's smallest number. E.g the column looks like this
Null
52
48.4
36.9
Null
Null
Null
Null
12
17
25.0
26.6
29.4
Null
Null
Null

And the result starts with "" because of the Null above followed by the smallest value of 36.9176 for the first array and then 12 for the last. The gaps represent the position of the "Null" word.

36.9
36.9
36.9




12
12
12
12
12


I have tried the codes but to no avail. Please anyone to help.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

How about just a formula?

Row\Col	A	B	C
1
2	Null	--	B2 and down: =IF(A2="Null", "--", MIN(INDEX(A$1:A2, LOOKUP(9E+307, ROW(A$1:A2) / (A$1:A2="Null"))):INDEX(A2:A$17, MATCH("Null", A2:A$17, 0))))
3	52	36.9
4	48.4	36.9
5	36.9	36.9
6	Null	--
7	Null	--
8	Null	--
9	Null	--
10	12	12
11	17	12
12	25	12
13	26.6	12
14	29.4	12
15	Null	--
16	Null	--
17	Null	--

Thank you shg. I did not mention that the first column is made up of variables and hence a formula isn't adequate. For instance, if all the cells in column A get filled with numbers, then all the cells in B should have a single number which must be the smallest of the numbers in A since the array then would be the whole of A. The trouble is automating the response to cover whatever degree that cells in A get filled with numbers.

If Null might naot appear above or below or both, in A2 and copy down,

=IF(A2="Null", "--", MIN(INDEX(A$1:A2, IFERROR(LOOKUP(9E+307, ROW(A$1:A2) / (A$1:A2="Null")), 1)) : INDEX(A2:A$17, IFERROR(MATCH("Null", A2:A$17, 0), ROWS(A2:A$17)))))

If a UDF is what you want,

Please Login or Register  to view this content.

Can you provide an example where it doesn't work?

No?

Row\Col	A	B	C
1
2	Null	--	B2 and down: =J(A:A)
3	52	36.9
4	48.4	36.9
5	36.9	36.9
6	Null	--
7	Null	--
8	Null	--
9	Null	--
10	12	12
11	17	12
12	25	12
13	26.6	12
14	29.4	12
15	Null	--
16	Null	--
17	Null	--