AverageIF that contain a text for visible/filtered cells only

[floW_5]

Sorry, newbie here. I've been looking around (probably not hard enough) for the most appropriate formula with the AVERAGEIF function to work with filtered/visible cells only.

These are the two formula i am using atm which is working perfectly fine to show all data. But not when i filter out certain criteria because it keeps recognizing all cell within the table. Screenshot also attached.
=IFERROR(AVERAGEIF(AA:AA,"P",AD:AD),"")
and
=IFERROR(AVERAGEIF(AA:AA,"P",AE:AE),"")

I am truly sorry if this post have been made many times before me. I have tried a bunch of different solution from here & reddit and maybe I'm inputting something wrong but nothing seems to work for me yet.

 

[floW_5]

Data1.xlsx
	A	B	C	D	E	F	G	H	I
1	DATA
2
3							Avg1	Avg2
4							5.89	7.21
5		Column4	Column5	Column21	Column22	Pass or Fail	Difference1	Difference2
6	Day 1			34.65	151.98	P
7	Day 2			41.64	448.92	F
8	Day 3			78.79	511.20	P
9	Day 4	80.65	519.64	79.12	634.78	F	1.02	0.82
10	Day 5	9.84	114.11	63.95	289.90	P	0.15	0.39
11	Day 6			31.87	261.15	P
12	Day 7	86.01	247.72	3.26	9.19	P	26.38	26.95
13	Day 8			57.13	265.97	F
14	Day 9	162.82	994.83	85.39	462.84	P	1.91	2.15
15	Day 10			113.72	231.42	P
16	Day 11			131.11	879.80	F
17	Day 12	28.14	91.55	82.94	169.86	P	0.34	0.54
18	Day 13	131.25	879.80	8.10	34.73	P	16.21	25.34
19	Day 14			37.82	123.59	P
20	Day 15			99.33	99.43	F
21	Day 16			47.11	227.55	P
22	Day 17	48.85	276.33	48.33	250.68	P	1.01	1.10
23	Day 18	5.22	36.82	31.29	124.21	P	0.17	0.30
24	Day 19	82.23	160.08	89.06	171.89	P	0.92	0.93
25	Day 20			61.49	381.22	F
26
27
DATA

Cell Formulas
Range	Formula
G4	G4	=IFERROR(AVERAGEIF(F6:F1000,"P",G6:G1000),"")
H4	H4	=IFERROR(AVERAGEIF(F6:F1000,"P",H6:H1000),"")
G6:H25	G6	=IF(AND(B6<>"",D6<>""),(B6/D6),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:F1048576	Cell Value	contains "F"	text	NO
F6:F1048576	Cell Value	contains "P"	text	NO

Hope this helps

 

[floW_5]

.

 

[Peter_SSs]

Unfortunately post #11 does not answer any of the 3 questions asked in post #10.

So, I will assume these answers

It looks like your image shows a formal Excel table at the left with only about 20 rows. Is that the case?

Yes, it is a formal table

If so, what is the name of the table?

Table1

What columns does the entire table occupy?

Columns B:H

IF those assumptions are correct then could you use this method?

Add a new column to the table as shown below. This column can then be hidden if you want.
Use the average formulas shown in row 4

floW_5.xlsm
	A	B	C	D	E	F	G	H	I
1	DATA
2
3							Avg1	Avg2
4							5.886829479	7.211847711
5		Column4	Column5	Column21	Column22	Pass or Fail	Difference1	Difference2	Vis
6	Day 1			34.645	151.976	P			1
7	Day 2			41.643	448.917	F			1
8	Day 3			78.787	511.195	P			1
9	Day 4	80.648	519.641	79.117	634.779	F	1.019351088	0.818617188	1
10	Day 5	9.843	114.114	63.95	289.902	P	0.153917123	0.393629571	1
11	Day 6			31.874	261.153	P			1
12	Day 7	86.014	247.72	3.26	9.193	P	26.38466258	26.9465898	1
13	Day 8			57.134	265.968	F			1
14	Day 9	162.82	994.829	85.394	462.835	P	1.906691337	2.149424741	1
15	Day 10			113.722	231.415	P			1
16	Day 11			131.106	879.795	F			1
17	Day 12	28.139	91.546	82.936	169.857	P	0.339285714	0.538959242	1
18	Day 13	131.247	879.795	8.097	34.725	P	16.20933679	25.33606911	1
19	Day 14			37.82	123.59	P			1
20	Day 15			99.33	99.429	F			1
21	Day 16			47.112	227.55	P			1
22	Day 17	48.846	276.334	48.332	250.677	P	1.010634776	1.102350834	1
23	Day 18	5.218	36.823	31.288	124.214	P	0.166773204	0.296448065	1
24	Day 19	82.234	160.082	89.062	171.889	P	0.923334306	0.931310322	1
25	Day 20			61.488	381.223	F			1
Sheet1

Cell Formulas
Range	Formula
G4	G4	=IFERROR(AVERAGEIFS(Table1[Difference1],Table1[Pass or Fail],"P",Table1[Vis],1),"")
H4	H4	=IFERROR(AVERAGEIFS(Table1[Difference2],Table1[Pass or Fail],"P",Table1[Vis],1),"")
G6:H25	G6	=IF(AND(B6<>"",D6<>""),(B6/D6),"")
I6:I25	I6	=SUBTOTAL(3,[@[Pass or Fail]])

 

[floW_5]

The table name is just called "Data" and Yes i believe just a normal table using Ctrl T and pressing enter.
I have tried your method, although it is what i believe you think I am trying to achieve but it is not. I blame myself for not explaining it clear enough. My apologies, English isn't my first language. Ill try my best to explain this again the best I can.

I am just after a formula to input in cell G4 to calculate the average from "Difference1" to only averageIF it contains the letter "P" from "Pass or Fail" column. Atm my formula and your updated one already does that, but as soon as I use the filter to hide certain value. The 5.89 should also change. But in this case, it remains 5.89. With the screenshot also attached, G4 value should now be showing 1.46 just averaging up G14 and G22, excluding G9 because it doesn't contain the "P" from "Pass or Fail".

I really hope this makes sense now. My apologies for the bad interpretation and i do appreciate your time and effort on trying to resolve this. Thank you very much Peter

Data1.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3							Avg1	Avg2
4							5.89	7.21
5	Column1	Column2	Column3	Column4	Column5	Pass or Fail	Difference1	Difference2	Vis
9	Day 12	80.65	519.64	79.12	634.78	F	1.02	0.82	1.00
14	Day 17	162.82	994.83	85.39	462.84	P	1.91	2.15	1.00
22	Day 6	48.85	276.33	48.33	250.68	P	1.01	1.10	1.00
26
27
DATA

Cell Formulas
Range	Formula
G4	G4	=IFERROR(AVERAGEIFS(Data[Difference1],Data[Pass or Fail],"P",Data[Vis],1),"")
H4	H4	=IFERROR(AVERAGEIF(F6:F1000,"P",H6:H1000),"")
G9:H9,G14:H14,G22:H22	G9	=IF(AND(B9<>"",D9<>""),(B9/D9),"")

 

[Peter_SSs]

In your XL2BB mini sheet in post #14, the 'Vis' column in the table appears to just contain hard-coded 1.00 values. It should contain the formula =SUBTOTAL(3,[@[Pass or Fail]]) as per the mini sheet in post #13

 

[floW_5]

In your XL2BB mini sheet in post #14, the 'Vis' column in the table appears to just contain hard-coded 1.00 values. It should contain the formula =SUBTOTAL(3,[@[Pass or Fail]]) as per the mini sheet in post #13

View attachment 104462

Yes! this works. Thank you very much sir you are the greatest I didn't notice the formula for I6. I am curious though, is there a method to not use the extra Vis column? If not how do you hide the extra column?

 

[Peter_SSs]

is there a method to not use the extra Vis column?

Yes, but the formula is much more cumbersome.

how do you hide the extra column?

Select the entire column by clicking it's letter label, right click and choose Hide