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?

 

[Domenic]

...I have been playing with INDIRECT on it's own and it doesn't want to accept the text portion combined with the countif function...

Okay, I think if the COUNTIF functions are entered separately in two cells, and the formula refers to the two cells instead, it should work...

A1:

=COUNTIF(D15:K15,">0")

B1:

=COUNTIF(D14:K14,">0")

Then try the following...

=LINEST(SUBTOTAL(9,OFFSET(D15:K15,,SMALL(IF(ISNUMBER(D15:K15)*(D15:K15>0),COLUMN(D15:K15)-COLUMN(D15)),ROW(INDIRECT("1:"&A1))),,1)),SUBTOTAL(9,OFFSET(D14:K14,,SMALL(IF(ISNUMBER(D14:K14)*(D14:K14>0),COLUMN(D14:K14)-COLUMN(D14)),ROW(INDIRECT("1:"&B1))),,1)))

Does this help?

 

[rrussell]

Sorry, but nothing has changed. Still doesn't like it.

 

[Aladin Akyurek]

I still need to exclude 0 and blank entries. I have built the references into the formula so I would be able to copy it into different tables without having to define new references.

Not because you would be interested, but others might...

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

A2:

=SUMPRODUCT(--(D14:K14<>""),--(D15:K15<>""))

A4, copied down:

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

B2:

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

B4:

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

C4:

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

which is confirmed with control+shift+enter then copied down.

 

[Domenic]

Sorry, but nothing has changed. Still doesn't like it.

Then it looks like the only way your version of Excel will handle that formula is by using defined references. If you'd like, I can email you a sample so that you can be sure...

 

[rrussell]

Sorry, but nothing has changed. Still doesn't like it.

Then it looks like the only way your version of Excel will handle that formula is by using defined references. If you'd like, I can email you a sample so that you can be sure...

Thanks for all your help. I don't think it is an issue with my version because the equations are working perfectly in other worksheets of the same workbook and in new workbooks. I have been banging my against a wall and can't figure out what is so different about the worksheet I need to use it in.

Trying to narrow down the problem I have ended up working with something as simple as CONCATENATE("1",":","10") which works everywhere else but in the sheet I need. Is it possible the one sheet is corrupted somehow?

 

[Domenic]

Thanks for all your help. I don't think it is an issue with my version because the equations are working perfectly in other worksheets of the same workbook and in new workbooks. I have been banging my against a wall and can't figure out what is so different about the worksheet I need to use it in.

Trying to narrow down the problem I have ended up working with something as simple as CONCATENATE("1",":","10") which works everywhere else but in the sheet I need. Is it possible the one sheet is corrupted somehow?

Maybe the numbers in the range are not being recognized as numerical values. Try to re-format the cells as 'General', and re-enter the values.

Does this help?

 

[rrussell]

Still no go. The equation wizard isn't even calculating the result as I fill in the fields for some reason, but only in this one worksheet. I am in the process of copying the data into a new file to see if it helps.

 

[Domenic]

Still no go. The equation wizard isn't even calculating the result as I fill in the fields for some reason, but only in this one worksheet. I am in the process of copying the data into a new file to see if it helps.

If it helps, great. Otherwise, if you want, you can email me a copy of your file and I'll see if I can pinpoint the problem.

 

[Domenic]

I've taken a look at your file, and there's no reason why the formula shouldn't work, at least from what I can see. The only thing I can think of is that there might be some sort of conflict between the VBA code and the formula. When I copy the whole sheet to a new file along with the formula, there's no problem. The formula returns the results. You said that the formula works in all other files. Do they contain VBA code as well, or is it just this one?

 

[rrussell]

Only this file had the VBA. You are right, if I copy the data and formula everything works. But the new file I just created still works with the VBA code too. Looks like it is going to work, but I'm not sure if we will know why there was a problem with first sheet.