Conditional average (index?)

[mischifous]

Hi excel wizards,


I have two large tables, I'm wanting to create a column, that will find the average of points in table 2, based on if the corresponding value in table 1 meets a certain condition. Is this possible to

For example,

"TABLE1"

	JOHN	SALLY	MAX
day 1	35%	22%	55%
day 2	28%	34%	52%

"TABLE2"

	JOHN	SALLY	MAX
day1	10	9	12
day2	8	5	11

In a single column, i want to calculate the average for each day where only the values that are >30% in table 1 are included to calculate the average:

thus, a new table/column that has the following values
day 1= average('TABLE2'!B2,'TABLE2'!D2)
day2 = average('TABLE2'!C2,'TABLE2'!D2)

(my data is actually much larger than this but it is in this format) There are also sometimes values in the format of "-" in TABL1.

As always, i really appreciate you guys' wizardry. This community has helped me so much over the years.

 

[Habtest]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mischifous]

Sure -- i'm using O365 and i have set it in my profile. Thanks.

 

[Habtest]

Great. If format and headers in table 1 and 2 are identical, this should work

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		JOHN	SALLY	MAX			JOHN	SALLY	MAX			Average
2	day 1	35%	22%	55%		day1	10	9	12		day 1	11
3	day 2	28%	34%	52%		day2	8	5	11		day 2	8
Sheet1

Cell Formulas
Range	Formula
L2:L3	L2	=AVERAGEIF(B2:D2,">0.3",G2:I2)

 

[Rahul1987]

You can al

Hi excel wizards,


I have two large tables, I'm wanting to create a column, that will find the average of points in table 2, based on if the corresponding value in table 1 meets a certain condition. Is this possible to

For example,

"TABLE1"

	JOHN	SALLY	MAX
day 1	35%	22%	55%
day 2	28%	34%	52%

"TABLE2"

	JOHN	SALLY	MAX
day1	10	9	12
day2	8	5	11

In a single column, i want to calculate the average for each day where only the values that are >30% in table 1 are included to calculate the average:

thus, a new table/column that has the following values
day 1= average('TABLE2'!B2,'TABLE2'!D2)
day2 = average('TABLE2'!C2,'TABLE2'!D2)

(my data is actually much larger than this but it is in this format) There are also sometimes values in the format of "-" in TABL1.

As always, i really appreciate you guys' wizardry. This community has helped me so much over the years.

How about

Book7
	A	B	C	D	E	F	G	H	I	J	K	L
1		JOHN	SALLY	MAX			JOHN	SALLY	MAX		Day	Output
2	day 1	35%	30%	55%		day1	10	9	12		day 1	11
3	day 2	28%	34%	52%		day2	8	5	11		day 2	8
Sheet1

Cell Formulas
Range	Formula
L2:L3	L2	=AVERAGE(IF($B2:$D2>0.3,$G2:$I2))
Press CTRL+SHIFT+ENTER to enter array formulas.