CODEWITHSUNDEEP
excelexcel-formula
Jorge Mendes
Jorge Mendes

Reputation: 97

excel min value different than 0 such that the next value is 0

I've the following matrix, and I need to get the hours of the minimum and maximum values from each block of ones.

See my example:

In my first attempt, I've obtained the first 1 and the last one (8:30 and 17:00) using the minifs and maxifs formula.

Since I couldn't obtain 11:00 and 12:30, I changed my approach. I've created a help matrix with 48 columns that gives 0,1,2,3 or 4 in the positions that I want. See it:

help matrix

However, doing this help matrix increases the file size a lot. I wish that I could get the middle hours by using just a formula. I don't want to use the help matrix.

Thanks in advance!

Upvotes: 0

Views: 68

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

Put this in the first cell and drag across and down:

=INDEX($1:$1,AGGREGATE(15,7,COLUMN($B2:$AV2)/(($A2:$AU2=--(ISEVEN(COLUMN(A1))))*($B2:$AV2=--(ISODD(COLUMN(A1))))),INT((COLUMN(A1)-1)/2)+1)-ISEVEN(COLUMN(A1)))

enter image description here

Upvotes: 2

Related Questions