CODEWITHSUNDEEP
excelexcel-formula
shaun21uk
shaun21uk

Reputation: 110

Excel: Lowest value in column A while Column B is not blank

In Column B I have the word "setup" which appears in sporadic blocks going down the column.

The blocks could be 2 rows high or 30 rows high.

In Column A I have a number.

In Column C I would like to pick out the lowest number from Column A while Column B is not empty and place it at the top of each block.

enter image description here

Can anybody help please?

Upvotes: 0

Views: 100

Answers (2)

JvdV
JvdV

Reputation: 75840

For example:

enter image description here

Formula in C2 and dragged down:

=IF(AND(B2="setup",B2<>B1),INDEX(A:A,MATCH(TRUE,ISBLANK(B2:B$1048576),0)+ROW(A1)-1),"")

With Excel O365, you could use:

=IF(AND(B4="setup",B4<>B3),XLOOKUP(TRUE,ISBLANK(B2:B$1048576),A1:A$1048575,,0),"")

Upvotes: 1

basic
basic

Reputation: 11968

You can try following formula:

=IFERROR(IFERROR(MIN(INDEX(A:A,AGGREGATE(14,6,(1/($B$1:B2=""))*ROW($B$1:B2),1)+1,1):INDEX(A:A,AGGREGATE(15,6,(1/(B2:$B$100000=""))*ROW(B2:$B$100000),1)-1,1))/(B2<>""),"")/(B1=""),"")

Replace $B$100000 with last used cell.

enter image description here

Upvotes: 1

Related Questions