Filter function cell reference

[rrussell]

I have a block of data, each row has a different number of populated columns. Some cells were left blank, and some have more data than others. My problem is that the function I am using to analize this data doesn't like blank cells, is there a way to reference the cells to only use the populated fields under the full range of cells that could be filled in?

 

[TrippyTom]

Highlight the full range and give it a name. Use the named range in whatever formulas you're working with.

 

[rrussell]

I tried naming the cells and the function worked fine until it ran into a blank field. The function I am using is =LINEST(D15:K15,D14:K14) and some of the cells in that range are blank.

 

[Aladin Akyurek]

I tried naming the cells and the function worked fine until it ran into a blank field. The function I am using is =LINEST(D15:K15,D14:K14) and some of the cells in that range are blank.

=LINEST(IF(D15:K15="",0,D15:K15),IF(D14:K14="",0,D14:K14))

which you need to confirm with control+shift+enter, not just with enter.

 

[rrussell]

I tried naming the cells and the function worked fine until it ran into a blank field. The function I am using is =LINEST(D15:K15,D14:K14) and some of the cells in that range are blank.

=LINEST(IF(D15:K15="",0,D15:K15),IF(D14:K14="",0,D14:K14))

which you need to confirm with control+shift+enter, not just with enter.

This does allow the function to still work, but the result is not correct because it uses the 0 value when it calculates the line.
I need it to skip the cell that is blank and only use populated cells. Thanks for your help.

 

[Domenic]

Try the following...

1) Define the following references...

Insert > Name > Define

Name: Array1

Refers to:

=SUBTOTAL(9,OFFSET(Sheet1!$D$15:$K$15,,SMALL(IF(Sheet1!$D$15:$K$15<>"",COLUMN(Sheet1!$D$15:$K$15)-COLUMN(Sheet1!$D$15)),ROW(INDIRECT("1:"&COUNTIF(Sheet1!$D$15:$K$15,">0")))),,1))

Click Add

Name: Array2

Refers to:

=SUBTOTAL(9,OFFSET(Sheet1!$D$14:$K$14,,SMALL(IF(Sheet1!$D$14:$K$14<>"",COLUMN(Sheet1!$D$14:$K$14)-COLUMN(Sheet1!$D$14)),ROW(INDIRECT("1:"&COUNTIF(Sheet1!$D$14:$K$14,">0")))),,1))

Click Ok

2) Then use the following formula...

=LINEST(Array1,Array2)

Change the sheet reference accordingly.

Hope this helps!

 

[rrussell]

It works perfectly (but I'm not exactly sure how - doesn't really matter). Thanks for your help.

 

[Aladin Akyurek]

I tried naming the cells and the function worked fine until it ran into a blank field. The function I am using is =LINEST(D15:K15,D14:K14) and some of the cells in that range are blank.

=LINEST(IF(D15:K15="",0,D15:K15),IF(D14:K14="",0,D14:K14))

which you need to confirm with control+shift+enter, not just with enter.

This does allow the function to still work, but the result is not correct because it uses the 0 value when it calculates the line.
I need it to skip the cell that is blank and only use populated cells. Thanks for your help.

Domenic's proposal would suffer under unequal number of empty cells. Hence what follows...

aaLINESTwithEmptyCells rrussell.xls
	A	B	C	D	E	F	G	H	I	J	K
2	4	-0.21429
3	Range-1	Range-2	Pos
4	1	3	1
5	3	6	3
6	2	6	4
7	6	3	8
8
9
10
11
12
13
14				1		3	2				6
15				3	4	6	6		7		3
16
Sheet1

Formulas...

A2:

=MIN(COUNT(D14:K14),COUNT(D15:K15))

B2:

=LINEST(B4:INDEX(B4:B65536,A2),A4:INDEX(A4:A65536,A2))

A4, copied down:

=IF(N($C4),INDEX($D$14:$K$14,1,$C4),"")

B4, cpoied down:

=IF(N($C4),INDEX($D$15:$K$15,1,$C4),"")

C4:

=IF(ROW()-ROW(C$4)+1<=$A$2,SMALL(IF(ISNUMBER($D$14:$K$14)*ISNUMBER($D$15:$K$15),COLUMN($D$14:$K$14)-COLUMN($D$14)+1),ROW()-ROW(C$4)+1),"")

which must be confirmed with control+shift+enter and copied down.

 

[rrussell]

Domenic's method will work for my situation but when I tried using it in another worksheet I had a problem with the INDIRECT function. For some reason it does not like the text part "1:" (result is #value). Any idea why it was working in one worksheet, but not the other. The cells seam to be formated the same.

 

[Aladin Akyurek]

Domenic's method will work for my situation but when I tried using it in another worksheet I had a problem with the INDIRECT function. For some reason it does not like the text part "1:" (result is #value). Any idea why it was working in one worksheet, but not the other. The cells seam to be formated the same.

What I suggested will work for any situation.

BTW, the following would be more appropriate in A2:

=SUMPRODUCT(--ISNUMBER(D14:K14),--ISNUMBER(D15:K15))