0

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?

2
  • Where are you returning the result to? Jul 30, 2013 at 15:03
  • In this simple example it could be to column E, rows 2 and 3 (with row 1 being the column heading) Jul 30, 2013 at 15:40

2 Answers 2

1

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")

1
  • Many thanks! that worked perfectly! you're a star! Jul 31, 2013 at 9:46
0

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).

3
  • 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 Jul 30, 2013 at 16:20
  • Sorry I hit return before I'd finished my response :-/ Jul 30, 2013 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, 2013 at 16:32

You must log in to answer this question.

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