I am using a forumla on filtered data - how to select only the data that is visible?

[cdossenb]

I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.

=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)

But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.


I am using Excel 2007 on XP.

 

[anglais428]

http://www.mrexcel.com/forum/showthread.php?t=45637

 

[barry houdini]

Hello cdossenb, welcome to MrExcel,

Domenic suggests a solution here for LINEST with a condition - you could amend that so that the condition is that the rows be visible (after filtering)....so that would make it:

=INDEX(LINEST(LN(N(OFFSET(R1059:R1167,SMALL(IF(SUBTOTAL(2,OFFSET(R1059:R1167,ROW(R1059:R1167)-ROW(R1059),0)),ROW(R1059:R1167)-ROW(R1059)),ROW(INDIRECT("1:"&SUBTOTAL(2,R1059:R1167)))),0,1))),N(OFFSET(W1059:W1167,SMALL(IF(SUBTOTAL(2,OFFSET(W1059:W1167,ROW(W1059:W1167)-ROW(W1059),0)),ROW(W1059:W1167)-ROW(W1059)),ROW(INDIRECT("1:"&SUBTOTAL(2,W1059:W1167)))),0,1))),1)

confirmed with CTRL+SHIFT+ENTER

 

[cdossenb]

Thank you both for your quick replies!!

I fear I not quite understand how to implement your suggestions - I think I will go with just copying the range and pasting somewhere else on the sheet. It is sort of slow and manual, but that's how it goes sometimes.

 

[Lori_M]

Some other suggestions are here:

http://newtonexcelbach.wordpress.com/2011/05/14/using-linest-on-data-with-gaps/

 

[Lori_M]

In addition, this array formula should handle filtered data:

=SLOPE(LN(R1059:R1167),IF(SUBTOTAL(2,OFFSET(W1059,ROW(W1059:W1167)-ROW(W1059),0)),W1059:W1167))

entered with CTRL+SHIFT+ENTER. Substitute SLOPE for INTERCEPT, RSQ, or STEYX for related LINEST stats, or use FORECAST in place of TREND.

You can check values against the exponential chart trendline values which also allows for filtered data. The exponent should match the slope and the log of the coefficient should match the intercept.

 

[cdossenb]

Thanks Lori M!
I think I'm almost there!

To get

Equation: y = c *e ^(b * x)
b: =INDEX(LINEST(LN,x),1)

I used your wonderful LINESTgap function (with a little help from the 'How to use Arrays' you posted - you are awesome!), but I tried changing
</pre>LinestGap = WorksheetFunction.LinEst(Ln(YA2), XA2, Const0, Stats)

And then I tried in a separate cell
=INDEX(V3847,1)

(V3847 is the test cell)

But the value isn't correct. Something is wrong. Any ideas where to add the LN?

 

[p45cal]

Try the likes of:

Excel Workbook
	A	B	C	D	E
1	x	y		0.059112	0.059112
4	4	49	1
5	4	46	1
8	5	42	1
11	6	48.5	1
12	6	41.8	1
13	7	54	1
14	7	47.2	1
15	7	44.8	1
16	8	48.2	1
Sheet

where the formula in E1 (Array-Entered this last essential) is more complex but doesn't need the helper column C which D1 (also array-entered) uses. Use the same technique for other functions.

 

[p45cal]

oops, trying to answer a query here:
http://www.mrexcel.com/forum/showthread.php?t=629092