Create a non-contiguous range

[MrPez]

I am trying to create a range to use linest. My data is not continuous and some entries may be errors/zeros. So I need some way to interrogate the data and only include the actual numbers in the range.

For example, say my potential data is in M33:M42,M47:M52,M56:M60 but only M33,M34,M35,M47,M48 actually contain values. The rest are #DIV/0 or #VALUE! errors due to other data being missing.

Any idea how I can accomplish this?

 

[XOR LX]

For example, say my potential data is in M33:M42,M47:M52,M56:M60

Hi.

By this do you mean that there is data in M43:M46 and M53:M55 which would interfere with any attempt to reference the range M33:M60 within any formula?

Regards

 

[shg]

One way, assuming there are no numbers in the between cells (because the function only accepts a single-area range):


[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][td]

R​

[/td][/tr][tr][td]

33​

[/td][td]

3​

[/td][td][/td][td]

2​

[/td][td]

1​

[/td][td][/td][td]O33:P33: {=LINEST(CloseRows(M33:M60))}[/td][/tr]
[tr][td]

34​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

35​

[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

36​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

37​

[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

38​

[/td][td]bob[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

39​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

40​

[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

41​

[/td][td]bob[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

42​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

43​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

44​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

45​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

46​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

47​

[/td][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

48​

[/td][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

49​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

50​

[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

51​

[/td][td]bob[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

52​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

53​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

54​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

55​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

56​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

57​

[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

58​

[/td][td]bob[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

59​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

60​

[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Function CloseRows(r As Range, _
                   Optional iMode As Long = 1) As Variant
  ' shg 2015
  ' Returns a variant array comprising the filled rows of r

  ' iMode
  '   1     keep numbers
  '   2     keep strings
  '   3     keep numbers & strings
  '   4     keep Booleans
  '   5     keep Booleans & numbers
  '   6     keep Booleans & strings
  '   7     keep everything except empty & errors

  Const iNum        As Long = 1
  Const iStr        As Long = 2
  Const iBoo        As Long = 4

  Dim av            As Variant
  Dim avOut         As Variant

  Dim iR            As Long
  Dim iW            As Long
  Dim iTop          As Long
  Dim jR            As Long
  Dim jTop          As Long

  av = r.Value2
  iTop = UBound(av, 1)
  jTop = UBound(av, 2)

  Do While iR < iTop
    iR = iR + 1
    For jR = 1 To UBound(av, 2)
      Select Case VarType(av(iR, jR))
        Case vbEmpty, vbError
          Exit For
        Case vbDouble
          If (iMode And iNum) = 0 Then Exit For
        Case vbString
          If (iMode And iStr) = 0 Then Exit For
        Case vbBoolean
          If (iMode And iBoo) = 0 Then Exit For
      End Select
    Next jR

    If jR <= jTop Then
      ' close up
      For iW = iR To iTop - 1
        For jR = 1 To UBound(av, 2)
          av(iW, jR) = av(iW + 1, jR)
        Next jR
      Next iW
      iTop = iTop - 1
      iR = iR - 1
    End If
  Loop

  ReDim avOut(1 To iTop, 1 To UBound(av, 2))
  For iR = 1 To iTop
    For jR = 1 To UBound(av, 2)
      avOut(iR, jR) = av(iR, jR)
    Next jR
  Next iR

  CloseRows = avOut
End Function

 

[XOR LX]

If that's the case then you can achieve the same result using worksheet formulas alone:

=LINEST(INDEX(M:M,N(IF(1,MODE.MULT(IF(ISNUMBER(M33:M60),{1,1}*ROW(M33:M60)))))))

with CSE**.


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[MrPez]

I don't understand either of your clever solutions, but that function seems to do what I want so thanks very much!

 

[shg]

That's clever, XOR LX.

The function was designed to accommodate a multi-column range, and return an array in which only rows with numbers straight across are preserved.

 

[Aladin Akyurek]

I don't understand either of your clever solutions, but that function seems to do what I want so thanks very much!

Shouldn't your non-contiguous range exclude in-between numbers?

 

[XOR LX]

That's clever, XOR LX.

The function was designed to accommodate a multi-column range, and return an array in which only rows with numbers straight across are preserved.

Ah, I see.

You'd just need a small adjustment for the equivalent formula set-up, e.g. for columns M and N:

=LINEST(INDEX($1:$1048576,N(IF(1,MODE.MULT(IF(ISNUMBER(M33:N60),{1,1}*ROW(M33:N60))))),N(IF(1,COLUMN(M:N)))))

Regards

 

[XOR LX]

Apologies. Of course the {1,1} shouldn't be in there if we're talking more than one column.

Should be:

=LINEST(INDEX($1:$1048576,N(IF(1,MODE.MULT(IF(ISNUMBER(M33:N60),ROW(M33:N60))))),N(IF(1,COLUMN(M:N)))))

Regards

 

[shg]

Also very nice.

The genesis of the function was to do a surface fit f(x,y) = mx*x + my*y + mxy*x*y + b:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]

x​

[/td][td]

y​

[/td][td]

f(x,y)​

[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

text​

[/td][td]

0.00​

[/td][td]

1.00​

[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

0.25​

[/td][td]

0.00​

[/td][td]

1.08​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

0.50​

[/td][td]

#N/A​

[/td][td]

1.15​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

0.75​

[/td][td]

0.00​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

#DIV/0!​

[/td][td]

0.00​

[/td][td]

1.30​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

0.00​

[/td][td]

0.25​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

0.25​

[/td][td]

0.25​

[/td][td]

1.01​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

0.50​

[/td][td]

0.25​

[/td][td]

1.11​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

0.75​

[/td][td]

0.25​

[/td][td]

1.22​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

1.00​

[/td][td]

0.25​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

0.00​

[/td][td]

0.50​

[/td][td]

0.80​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

0.25​

[/td][td]

0.50​

[/td][td]

text​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

#DIV/0!​

[/td][td]

0.50​

[/td][td]

1.08​

[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

0.75​

[/td][td]

text​

[/td][td]

text​

[/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

1.00​

[/td][td]

0.50​

[/td][td]

1.35​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

#DIV/0!​

[/td][td]

0.75​

[/td][td]

text​

[/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

#N/A​

[/td][td]

0.75​

[/td][td]

0.87​

[/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

0.50​

[/td][td]

0.75​

[/td][td]

#DIV/0!​

[/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

#DIV/0!​

[/td][td]

#DIV/0!​

[/td][td]

1.21​

[/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

1.00​

[/td][td]

0.75​

[/td][td]

1.38​

[/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

0.00​

[/td][td]

#DIV/0!​

[/td][td]

0.60​

[/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td]

0.25​

[/td][td]

1.00​

[/td][td]

0.80​

[/td][td][/td][td][/td][/tr]
[tr][td]

24​

[/td][td]

0.50​

[/td][td]

#N/A​

[/td][td]

#N/A​

[/td][td][/td][td][/td][/tr]
[tr][td]

25​

[/td][td]

0.75​

[/td][td]

1.00​

[/td][td]

1.20​

[/td][td][/td][td][/td][/tr]
[tr][td]

26​

[/td][td]

1.00​

[/td][td]

#N/A​

[/td][td]

text​

[/td][td][/td][td][/td][/tr]
[tr][td]

27​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

28​

[/td][td]

mx​

[/td][td]

my​

[/td][td]

mxy​

[/td][td]

b​

[/td][td][/td][/tr]
[tr][td]

29​

[/td][td]

0.3​

[/td][td]

-0.4​

[/td][td]

0.5​

[/td][td]

1​

[/td][td]A29:D29: {=LINEST(INDEX(v(CloseRows(A2:C26)),0,3), CHOOSE({3,2,1}, INDEX(v(),0,1), INDEX(v(),0,2), INDEX(v(),0,1) * INDEX(v(),0,2)), , TRUE)}[/td][/tr]
[tr][td]

30​

[/td][td]

1.3E-16​

[/td][td]

1.1E-16​

[/td][td]

2.2E-16​

[/td][td]

6.32063E-17​

[/td][td][/td][/tr]
[tr][td]

31​

[/td][td]

1​

[/td][td]

5.3E-17​

[/td][td]

#N/A​

[/td][td]

#N/A​

[/td][td][/td][/tr]
[tr][td]

32​

[/td][td]

4.3E+31​

[/td][td]

5​

[/td][td]

#N/A​

[/td][td]

#N/A​

[/td][td][/td][/tr]
[tr][td]

33​

[/td][td]

0.35164​

[/td][td]

1.4E-32​

[/td][td]

#N/A​

[/td][td]

#N/A​

[/td][td][/td][/tr]
[/table]