Microsoft Excel – Formula to Get First and Last Non-Empty Value in a Row

microsoft excelworksheet-function

Let's say I have an Excel spreadsheet aggregating votes in some poll over a period of time as per the example below.

How do I find the min and max value people have voted on on a given day (the column header)? 

         A        B   C   D   E   F   G    H     I   
   --------------------------------------------------
1  | Answers:   | 0 | 1 | 2 | 3 | 4 | 5 | Min | Max |
   --------------------------------------------------
2  | 2014-12-01 |   | 2 | 4 | 1 |   |   |  1  |  3  |
3  | 2015-01-01 |   |   | 1 | 4 | 2 |   |  2  |  4  |
                          ^       ^        ^-----^- Min: 2, Max: 4
                          |       |        
                          |       |_ 2 people voted "4"
                          |
                          |_ 1 person voted "2" on 2015-01-01

Best Answer

To find the min value people have voted on in the poll (the column header of the first non-empty cell), I used the following formula (in cell H2):

=INDEX(B$1:G$1, MATCH(TRUE, INDEX(B2:G2<>"", 0), 0))

To find the max value (in cell I2):

=LOOKUP(2, 1/(B2:G2<>""),B$1:G$1)

Under the bonnet

Lookup function has the following signature: LOOKUP( value, lookup_range, [result_range] )

What the second formula does is the following:

  1. Divide 1 by an array of boolean (true/false) values (B2:G2<>""); that's the 1/(B2:G2<>"") part.
  2. Above relies on a fact that Excel converts boolean TRUE to 1 and FALSE to 0, thus the result of this step is a list of:
    • 1's, where CELL_VALUE<>"" returned TRUE, and 1/TRUE => 1)
    • and #DIV/0! errors, where CELL_VALUE<>"" returned FALSE and 1/FALSE => 1/0 => #DIV/0!
  3. Now, because I used 2 (you can use an number starting from 1) as the lookup value, the formula matches the last numeric value in the range (as you remember, the range is something along the lines of [DIV/0!, 1, 1, 1, DIV/0!, DIV/0!], that's why lookup will match the fourth item). BTW: Note that if the formula didn't match anything you'd still get a #N/A error.
  4. At this stage we know what column we're interested in (fourth in the range). So the last bit that's missing is telling Excel that the result range is the first row of the spreadsheet: B$1:G$1, and that's where Excel will look for the said fourth item.

You can read more on Excel lookup formulas here: http://blogs.office.com/2012/04/26/using-multiple-criteria-in-excel-lookup-formulas/

Hope this helps!

Jan

Related Question