CODEWITHSUNDEEP
excelexcel-formula
realbananas
realbananas

Reputation: 13

Excel: Find lowest value that has boolean in second column

I've looked at this, which is very much like my problem, but there's a twist, and I'm not sure how to do what I specifically need.

I have two columns, one for the date and one containing a boolean "TRUE/FALSE". The date is formatted as an excel date. I would like to find the first and last date that has a boolean "TRUE". Ideally I'd like to solve the problem without resorting to VBA macros.

The twist when compared tot the other stackoverflow question is that I want to find the lowest value of the date, not the number of people in his example. I would also like to not need to sort the table, because the "TRUE" and "FALSE" will be changing due to other (manually changeable) factors, for which sorting the list (16k+ rows) each time you make a change would take too much time.

Date - Bool
14-5 - FALSE
15-5 - TRUE
16-5 - FALSE
01-6 - FALSE
20-6 - TRUE
17-5 - FALSE
18-5 - TRUE
19-5 - TRUE

Note that the list is not sorted on date

Result should be:
First: 15-5
Last: 20-6

Upvotes: 1

Views: 102

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You can do this simply with array formulas. Array formula are entered by holding down ctrl-shift while hitting enter. If you do it correctly, in the formula bar you will see that Excel placed braces {...} around the formula:

=MIN(IF(Bool,Dates))
=MAX(IF(Bool,Dates))

Where Bool and Dates are the named ranges of the designated columns.

Upvotes: 2

Related Questions