Calculations outside table does not react on filter within table

[DeThomaso]

Hi

Perhaps some of you will respond to this question to encourage me to use Pivot Table and I will eventually do that, but for the moment I need to see if there is another solution to this.

Here is the thing, I have a table with several rows which I can use filter to exclude data and only look at the data I need.
Outside the table I have a classic Bell Curve (x/y plot) of my data.
The Bell Curve is based on the data within the table.
But the data is measured over time and I want to see how the Bell Curve and other types of calculations differ using filter in table.

I use the formula "=STDEV.P(DataTable[Actual_value])" in one cell outside the table.
Were the table name is "DataTale" and the row "Actual_Value" is the data used for calculating.
I am using structured references with Excel tables and thought that this might do the trick.
But this does not adapt to the filter I am using in table.

I have tried searching for a solution to this without luck.
How can I expand the formula to only take the data that is filtered out?
One logic code if it would excist is:
"=STDEV.P(DataTable[#Filter[Actual_value]])"
or
"=STDEV.P(DataTable[Actual_value@Filter])"
or
"=STDEV.P(DataTable[Actual_value]by Filter)"

Is there anybody that can help me?

Thanks

 

[DRSteele]

If I understand correctly, the thing to do is use function SUBTOTAL with the appropriate argument for its base function, in your case std.dev like I did here in G7. I applied a slicer to the data to filter it. When I selected a few of the values in "target", the formula in G7 obeyed the filtering, while the one in G6 did not.

MrExcel posts19.xlsx
	C	D	E	F	G	H	I	J
5
6					23.49159	=STDEV.P(Table1[observation])
7					23.49159	=SUBTOTAL(8,Table1[observation])
8	target	observation
9	6	6.8
10	79	78
11	28	27.5
12	12	11.5
13	27	27.8
14	60	60.5
15	75	75.2
16	52	52.5
17	12	13
18	34	34.9
19	62	62.4
20	62	62.5
21	37	38
Sheet7

Cell Formulas
Range	Formula
G6	G6	=STDEV.P(Table1[observation])
H6:H7	H6	=FORMULATEXT(G6)
G7	G7	=SUBTOTAL(8,Table1[observation])

 

[DeThomaso]

Works great thanks
?

 

[DeThomaso]

Hi again

A complementary quastion to the same subject.

This formula looks at a row in the table above and check if all values have PASS or FAIL:
"=AND((EXACT(AllData!O2:O337;"PASS")))"
How can I get this to follow the filter?

Thanks

 

[DeThomaso]

Sorry the original formula were:
"=AND((EXACT(DataTable[PASS_FAIL];"PASS")))"
This is the one I want to follow Filter in table.

I am fiddling with the formula to see if I can have go around this instead, but the AND function gets error:
"=FILTER(DataTable[PASS_FAIL];ISNUMBER(SEARCH("FAIL";DataTable[PASS_FAIL]));"FAIL")"

 

[DRSteele]

I don't understand. Please put some sample data into the example I provided and post it. You can use the add-in xl2bb in my signature below.

Also, indicate what you are looking for and include the desired outcome.

 

[DeThomaso]

Here is an example of the code I want to use.
The formula on top is the one that is not doing what I like it to do.
If I filter on PASS_FAIL it will still do the formula on complete table.
I want it to adapt to the filter as the graphs does and what Subtotal does.

Thanks

 

[DRSteele]

MrExcel posts19.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6					23.49159	=STDEV.P(Table1[observation])				42.35385			all pass?	No
7					23.49159	=SUBTOTAL(8,Table1[observation])				42.35385
8	target	observation	p_f	pf helper
9	6	6.8	pass	1
10	79	78	pass	1
11	28	27.5	pass	1
12	12	11.5	pass	1
13	27	27.8	pass	1
14	60	60.5	pass	1
15	75	75.2	pass	1
16	52	52.5	pass	1
17	12	13	pass	1
18	34	34.9	pass	1
19	62	62.4	pass	1
20	62	62.5	pass	1
21	37	38	fail	0
Sheet7

Cell Formulas
Range	Formula
G6	G6	=STDEV.P(Table1[observation])
H6:H7	H6	=FORMULATEXT(G6)
G7	G7	=SUBTOTAL(8,Table1[observation])
P6	P6	=IF(SUBTOTAL(9,Table1[pf helper])=SUBTOTAL(2,Table1[pf helper]),"Yes","No")
L6	L6	=SUBTOTAL(9,Table1[observation])/SUBTOTAL(2,Table1[observation])
L7	L7	=SUBTOTAL(1,Table1[observation])
F9:F21	F9	=--([@[p_f]]="pass")

 

[DRSteele]

The formula in M6 obeys the filtering.

MrExcel posts19.xlsx
	C	D	E	F	G	H	I	J	K	L	M
6					23.49159	=STDEV.P(Table1[observation])				all pass?	No
7					23.49159	=SUBTOTAL(8,Table1[observation])
8	target	observation	p_f	pf helper
9	6	6.8	pass	1
10	79	78	pass	1
11	28	27.5	pass	1
12	12	11.5	pass	1
13	27	27.8	pass	1
14	60	60.5	pass	1
15	75	75.2	pass	1
16	52	52.5	pass	1
17	12	13	pass	1
18	34	34.9	pass	1
19	62	62.4	pass	1
20	62	62.5	pass	1
21	37	38	fail	0
Sheet7

Cell Formulas
Range	Formula
G6	G6	=STDEV.P(Table1[observation])
H6:H7	H6	=FORMULATEXT(G6)
G7	G7	=SUBTOTAL(8,Table1[observation])
M6	M6	=IF(SUBTOTAL(9,Table1[pf helper])=SUBTOTAL(2,Table1[pf helper]),"Yes","No")
F9:F21	F9	=--([@[p_f]]="pass")

 

[DeThomaso]

Great workaround, thanks.