3

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
0

1 Answer 1

3

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

2
  • Nice. Adding the example of how it can be used is great. I see you've already gotten some upvotes (I'm adding my own). Two thoughts: 1) You don't need the disclaimer, just the answer. 2) It isn't obvious why or how the max formula works. To make the answer even better (especially when you use a novel approach), add a brief explanation of how it works. 3) I couldn't figure out how to capture the direct link to a comment until I saw how you did it. You're teaching me all kinds of good stuff.
    – fixer1234
    Jan 29, 2015 at 18:23
  • You're right. The LOOKUP formula bit was not obvious and I edited my answer to explain it better. Hope someone finds it useful :)
    – Jan Molak
    Jan 30, 2015 at 12:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .