Solved: Tricky problem regardin comparing data

[Sir Babydum GBE]

The problem is stated on the attached but basically involves subtracting the first cell with numerical data from the next one.

Thanks

Sir BD

[mdmackillop]

A UDF Solution.
In E2 enter =Good2Days(B2)

Option Explicit
Function Good2Days(Data As Range)
Dim i As Long, j As Long
If IsNumeric(Data.Offset(, 1)) And IsNumeric(Data.Offset(, 4)) Then
Good2Days = Data.Offset(, 1) - Data.Offset(, 4)
Else
i = 3
Do
i = i + 1
Loop Until IsNumeric(Data.Offset(, i))
j = i
Do
j = j + 1
Loop Until IsNumeric(Data.Offset(, j))
Good2Days = Data.Offset(, i) - Data.Offset(, j)
End If

End Function


I'm looking forward to the ingenious formula solution!

[mikerickson]

The array formula
=INDEX(C4:J4,1,SMALL(IF(ISNUMBER(C4:J4),COLUMN(C4:J4)-2,99),1))

will return the left most number in c4:j4

=INDEX(C4:J4,1,SMALL(IF(ISNUMBER(C4:J4),COLUMN(C4:J4)-2,99),2))
will return the second left most number in that same range.

Since the result cell is in that range, some clumbsy looking IFs are needed to skip it, is there any chance of the result column being outside the input columns?

(array formulas are confirmed with Ctrl-Shift-Enter (Cmd-Return for Mac))

[Bob Phillips]

This is an array formula BD, but it depends upon you moving column C over in fromt of column F, so the numbers are in a block

=INDEX(A3:J3,,MIN(IF(ISNUMBER(E3:J3),COLUMN(E3:J3))))-INDEX(A3:J3,,SMALL(IF(ISNUMBER(E3:J3),COLUMN(E3:J3)),2))

[Sir Babydum GBE]

Thanks guys - the UDF is great! and, though I can't move the column (trust me, i'd like to) I could of course have 6 hidden columns that return the values in a block, and then run the array suggestions against the hidden columns.

I'm not a great lover of hidden columns because I forget they're there and then delete them - but that won't happen here.

Thanks again.

[Bob Phillips]

Okay, a bit longer but same principle

=IF(ISNUMBER(C3),
C3-INDEX(A3:J3,,SMALL(IF(ISNUMBER(F3:J3),COLUMN(F3:J3)),1)),
INDEX(A3:J3,,MIN(IF(ISNUMBER(F3:J3),COLUMN(F3:J3))))-INDEX(A3:J3,,SMALL(IF(ISNUMBER(F3:J3),COLUMN(F3:J3)),2)))