How to circumvent the depth limitation of Excel's IF function?

Question

For an Excel formula I need the first cell out of a list of cells which contains a numeric value:

 A  |  B  |  C  | ... |  Z  |
-----------------------------
    | 0.1 | 0.4 | ... | =0.1
    |     | 0.2 | ... | =0.2

I use this schema:

IF(ISNUMERIC(A1);A1;IF(ISNUMERIC(B1);A2;IF(ISNUMERIC(C1);C1;IF(...))))))))

Unfortunately this only works for seven columns, because the maximum length is limited in Excel.

Is there any way to re-phrase this formula so that it doesn't get deeper with every additional column?

Answer 1

OK, lets see. Try this

=INDEX(A1:Y1,SUMPRODUCT((A1:Y1="")*1)+1)

The sumproduct counts the number of blanks, then the index looks up the value in the cell where number of blanks + 1

Hope that helps.

Answer 2

Perhaps this may help XL: How to Determine Top/Bottom Used Cells in a Sparse Array

Answer 3

In a single cell you can do this with an array formula:

Isnumber provides the test in Excel 2007

Multiply the result by column()

Use an if statement to help the following min function along:

Use the min function to identify the first numeric column.

Remember to use ctrl shift enter when you want to make an array formula, not just enter.

={MIN(IF(ISNUMBER(A1:Z1),COLUMN(A1:Z1),5000))}

This also finds the first used column across multiple rows should you need that functionality.

Answer 4

astanders answer works (with the assumption that the cells following the first number are allways filled).

You can also write your own function in a VBA Module.

Public Function getFirstNumber(ByRef sourceRow As Range) As Double
    For Each Cell In sourceRow.Cells
        If WorksheetFunction.IsNumber(Cell) = True Then
            getFirstNumber = Cell.Value
            Exit Function
        End If
    Next Cell
End Function