Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to figure out a formula to identify in which column the value falls below 0.

I have thousands of rows and dozens of columns, so doing this manually isn't an option

So if my columns and values are as follows:

    A   B   C   D
1   Jul Aug Sep Oct
2   25  10  5   -2
3   10  2   -6  -10

So for row 2 I would like to return the result "Oct" (or 4 as the 4th column)as this is the month the value drops below 0. For row 3 I would like to return the value "Sep" (or 3 as the 3rd column).

Any ideas?

share|improve this question
    
Where are you returning the result to? –  Raystafarian Jul 30 '13 at 15:03
    
In this simple example it could be to column E, rows 2 and 3 (with row 1 being the column heading) –  Dave Barnett Jul 30 '13 at 15:40

2 Answers 2

You can do this with an array formula. In row 2, select your cell where you want the formula, paste the following into the formula bar, and press Ctrl+Shift+Enter.

=INDEX($A$1:$D$1,MIN(IF(A2:D2<0,COLUMN(A2:D2))))

For reference, A1:D1 is the range of column headers (months), and A2:D2 are the values in the row. What this formula does is look for values less than 0 and then takes the one with smallest column number. It then returns the month header in that column number.

This formula can be filled down to all rows.

EDIT:
If your data does not start at column A, you will have to make a small adjustment to the formula to get it to work. Everything stays the same in the formula except the first argument of the INDEX function must be extended to column A. For example, if your data starts in column C, then you would use the following formula instead (entered as an array formula).

=INDEX($A$1:$F$1,MIN(IF(C2:F2<0,COLUMN(C2:F2))))

The reason for this is that the INDEX function's second argument indicates the position in the array, not necessarily the column number. This distinction is obliterated if you just extend the array all the way to column A though (i.e., position in array will equal column number).

share|improve this answer
    
Thanks for that. –  Dave Barnett Jul 30 '13 at 16:20
    
Hi thanks for that... not quite working however. I've entered the formula =INDEX(AW$1:BM$1,MIN(IF(AW10:BM10<0,COLUMN(AW10:BM10)))) in cell AV10 as my test data is in the range AW10:BM10..... the titles are in row 1. The first column where the data falls below 0 is BM. When I enter the formula above it returns the error #VALUE!.... any ideas? Thanks again –  Dave Barnett Jul 30 '13 at 16:20
    
Sorry I hit return before I'd finished my response :-/ –  Dave Barnett Jul 30 '13 at 16:24
    
Ah ok, the issue is that your data does not start at column A as your example showed. Try this instead: =INDEX(A$1:BM$1,MIN(IF(AW10:BM10<0,COLUMN(AW10:BM10)))). –  Excellll Jul 30 '13 at 16:32
    
Many thanks for all your help! :-) –  Dave Barnett Jul 31 '13 at 9:47

It's probably a little simpler to use INDEX/MATCH e.g. this array formula in AV10

=INDEX(AW$1:BM$1,MATCH(TRUE,AW10:BM10<0,0))

confirmed with CTRL+SHIFT+ENTER

or if you prefer to avoid CSE then add another INDEX function like this and formula can be entered "normally"

=INDEX(AW$1:BM$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0))

If there are no numbers < 0 then that will return #N/A error......or you can use IFERROR function to replace with some text, e.g.

=IFERROR(INDEX(AW$1:BM$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0)),"No negative numbers")

share|improve this answer
    
Many thanks! that worked perfectly! you're a star! –  Dave Barnett Jul 31 '13 at 9:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.