Average with certain conditions

Junaid Azhar

New Member
Joined
Mar 30, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Would like to average with certain condition e.g. Sector/Category , Asset lite & 1 Year TSR from below mentioned table.

1 Year TSR
Sector/CategoryAsset LiteAsset Heavy
Household Products
Beverages
A&D
Health Care Equipment
Manufacturing
Food
Chemicals



Sector3 Year Avg. PPE/Sales5 Year Avg. PPE/Sales3 Year Avg. Asset Turnover5 Year Avg. Asset Turnover3 Year Avg. ROA5 Year Avg. ROA3 Year Avg. ROCE5 Year Avg. ROCE3 Year Avg. ROIC5 Year Avg. ROIC3 Year Avg. EBITDA Margin5 Year Avg. EBITDA Margin1 Year TSR3 Year TSR5 Year TSRCategory
Household Products
30.9%​
30.1%​
56.4%​
55.1%​
7.4%​
6.9%​
15.7%​
14.7%​
14.1%​
13.3%​
25.4%​
24.9%​
14.4%​
10.3%​
9.8%​
Asset Heavy
Beverages
27.8%​
27.4%​
41.9%​
44.5%​
7.3%​
7.2%​
23.5%​
25.1%​
12.8%​
14.0%​
31.5%​
29.9%​
17.0%​
10.8%​
8.8%​
Asset Lite
Beverages
28.5%​
27.5%​
82.8%​
84.9%​
8.5%​
8.7%​
28.1%​
27.2%​
23.0%​
22.6%​
19.5%​
19.5%​
14.5%​
9.0%​
9.8%​
Asset Lite
A&D
14.6%​
14.0%​
75.7%​
86.6%​
3.9%​
4.2%​
26.3%​
25.2%​
229.7%​
208.2%​
9.0%​
9.2%​
-48.4%​
4.2%​
7.2%​
Asset Lite
Health Care Equipment
26.9%​
27.2%​
42.8%​
43.5%​
3.6%​
3.9%​
8.1%​
10.1%​
5.8%​
7.5%​
23.4%​
22.7%​
1.4%​
21.8%​
13.0%​
Asset Heavy
Health Care Equipment
13.8%​
16.3%​
32.2%​
28.9%​
4.3%​
4.1%​
9.5%​
9.8%​
7.4%​
7.5%​
30.7%​
30.5%​
2.1%​
5.9%​
6.1%​
Asset Lite
Manufacturing
14.5%​
14.7%​
67.7%​
70.8%​
8.1%​
8.3%​
17.0%​
18.5%​
20.8%​
21.5%​
21.8%​
21.0%​
2.0%​
10.2%​
12.0%​
Asset Lite
A&D
18.5%​
17.4%​
55.5%​
58.9%​
4.9%​
5.2%​
10.9%​
12.1%​
9.6%​
11.0%​
17.3%​
17.5%​
-11.9%​
0.8%​
0.4%​
Asset Lite
A&D
11.9%​
12.1%​
117.6%​
106.8%​
8.9%​
7.9%​
20.5%​
18.9%​
34.9%​
31.1%​
14.0%​
13.6%​
21.2%​
12.7%​
15.6%​
Asset Lite
Health Care Equipment
15.4%​
15.2%​
32.6%​
33.0%​
3.8%​
3.7%​
8.0%​
7.9%​
6.7%​
6.7%​
24.7%​
24.0%​
9.9%​
17.4%​
17.3%​
Asset Lite
Manufacturing
28.8%​
28.6%​
81.7%​
85.7%​
12.3%​
12.8%​
22.2%​
22.6%​
24.5%​
25.2%​
27.6%​
27.8%​
-25.9%​
-5.7%​
0.8%​
Asset Lite
Manufacturing
49.2%​
47.3%​
31.3%​
30.0%​
0.6%​
0.8%​
0.8%​
1.3%​
1.1%​
1.6%​
8.8%​
10.9%​
-15.5%​
-33.1%​
-17.4%​
Asset Heavy
Food
34.1%​
32.4%​
40.9%​
42.4%​
3.9%​
3.7%​
13.3%​
12.8%​
8.0%​
8.0%​
18.6%​
17.6%​
13.8%​
8.3%​
10.8%​
Asset Heavy
Health Care Equipment
17.5%​
15.7%​
51.8%​
54.4%​
7.6%​
8.0%​
17.0%​
21.1%​
14.1%​
17.3%​
27.2%​
27.2%​
-7.0%​
11.8%​
15.4%​
Asset Lite
Manufacturing
27.0%​
31.0%​
65.8%​
61.8%​
5.9%​
4.6%​
19.1%​
13.3%​
10.6%​
7.4%​
19.5%​
17.6%​
-21.8%​
5.7%​
8.2%​
Asset Lite
Health Care Equipment
34.9%​
35.1%​
31.7%​
36.5%​
3.9%​
4.5%​
9.5%​
11.9%​
7.5%​
9.4%​
29.1%​
27.9%​
-6.3%​
9.2%​
11.9%​
Asset Heavy
Health Care Equipment
23.8%​
21.3%​
49.3%​
47.6%​
10.7%​
10.6%​
73.5%​
77.8%​
28.1%​
26.4%​
36.2%​
36.6%​
-7.4%​
27.7%​
25.4%​
Asset Heavy
Household Products
26.1%​
25.5%​
118.0%​
122.8%​
18.4%​
19.1%​
41.6%​
42.3%​
45.4%​
46.8%​
27.2%​
27.6%​
7.4%​
-0.1%​
2.5%​
Asset Heavy
Beverages
52.4%​
49.7%​
52.5%​
52.3%​
5.0%​
4.9%​
11.5%​
11.2%​
8.8%​
8.6%​
21.6%​
21.4%​
-9.7%​
4.8%​
5.0%​
Asset Heavy
Chemicals
27.8%​
26.6%​
71.3%​
71.6%​
6.9%​
6.8%​
14.1%​
13.8%​
12.3%​
12.0%​
21.6%​
21.6%​
10.2%​
15.8%​
11.5%​
Asset Lite
A&D
20.8%​
18.0%​
78.8%​
85.8%​
7.0%​
7.2%​
14.9%​
14.7%​
21.1%​
21.9%​
15.8%​
15.2%​
17.3%​
11.0%​
16.9%​
Asset Heavy
Health Care Equipment
19.8%​
19.8%​
43.1%​
43.4%​
5.0%​
4.8%​
11.3%​
12.5%​
7.6%​
8.9%​
26.6%​
26.2%​
-15.9%​
11.2%​
15.3%​
Asset Heavy
Manufacturing
12.8%​
12.4%​
92.8%​
90.6%​
13.9%​
13.0%​
25.1%​
24.5%​
25.2%​
23.7%​
27.3%​
26.5%​
13.0%​
8.3%​
12.7%​
Asset Lite
Manufacturing
16.0%​
17.0%​
50.4%​
49.3%​
3.5%​
3.2%​
8.7%​
7.7%​
5.6%​
5.1%​
15.7%​
15.0%​
-6.6%​
12.0%​
12.8%​
Asset Lite
A&D
12.5%​
11.0%​
83.1%​
81.8%​
7.0%​
6.9%​
14.5%​
13.8%​
21.7%​
20.5%​
15.0%​
15.0%​
-11.2%​
2.4%​
10.3%​
Asset Lite
Chemicals
110.0%​
110.4%​
46.0%​
44.9%​
6.7%​
6.1%​
14.3%​
12.3%​
13.7%​
11.7%​
34.2%​
32.8%​
15.4%​
17.3%​
10.8%​
Asset Heavy
Household Products
40.6%​
39.8%​
123.1%​
123.9%​
13.6%​
13.8%​
27.6%​
27.0%​
33.6%​
33.7%​
21.4%​
21.7%​
17.3%​
3.4%​
8.3%​
Asset Heavy
A&D
12.8%​
12.1%​
82.9%​
88.0%​
6.9%​
7.2%​
15.2%​
16.6%​
20.3%​
22.5%​
14.5%​
14.4%​
-10.0%​
-6.2%​
4.4%​
Asset Lite
Chemicals
14.1%​
12.2%​
84.8%​
125.2%​
7.2%​
11.4%​
19.2%​
26.6%​
15.1%​
26.3%​
15.1%​
15.6%​
24.2%​
19.7%​
13.9%​
Asset Lite
Health Care Equipment
43.1%​
43.1%​
65.0%​
61.6%​
6.7%​
5.2%​
16.5%​
13.9%​
15.9%​
13.8%​
22.9%​
20.6%​
4.1%​
15.6%​
17.7%​
Asset Heavy
A&D
14.6%​
14.9%​
59.4%​
57.3%​
5.2%​
5.5%​
14.8%​
12.8%​
14.9%​
13.2%​
21.9%​
22.4%​
18.0%​
21.2%​
23.5%​
Asset Lite
Health Care Equipment
23.1%​
21.6%​
65.9%​
65.0%​
12.6%​
12.1%​
37.2%​
37.1%​
32.6%​
32.2%​
31.2%​
30.4%​
12.5%​
28.6%​
23.9%​
Asset Heavy
Manufacturing
20.7%​
20.1%​
84.3%​
78.7%​
8.8%​
8.4%​
19.8%​
17.9%​
19.5%​
18.2%​
20.6%​
21.0%​
-20.2%​
-1.4%​
2.1%​
Asset Lite
Beverages
23.5%​
22.3%​
22.5%​
24.0%​
2.0%​
2.9%​
8.7%​
8.0%​
5.5%​
5.7%​
26.9%​
26.8%​
-8.1%​
NANAAsset Lite
Manufacturing
4.4%​
4.0%​
32.0%​
31.5%​
5.6%​
5.8%​
12.1%​
11.8%​
9.4%​
9.3%​
34.5%​
34.5%​
0.3%​
15.9%​
14.8%​
Asset Lite
Beverages
7.0%​
6.1%​
78.7%​
71.7%​
17.7%​
16.8%​
52.0%​
52.4%​
36.3%​
39.0%​
36.4%​
37.1%​
2.6%​
9.7%​
6.5%​
Asset Lite
Beverages
60.6%​
55.4%​
34.7%​
36.5%​
7.4%​
7.2%​
14.5%​
13.0%​
11.6%​
10.6%​
35.5%​
33.3%​
-6.7%​
0.8%​
7.2%​
Asset Heavy
Food
23.9%​
23.2%​
59.7%​
67.2%​
6.9%​
7.3%​
15.9%​
15.5%​
13.4%​
13.2%​
21.6%​
20.7%​
17.2%​
-0.6%​
3.9%​
Asset Lite
Food
28.4%​
28.9%​
111.5%​
121.5%​
15.7%​
15.8%​
32.3%​
29.7%​
29.5%​
28.0%​
24.9%​
23.7%​
37.6%​
13.7%​
11.0%​
Asset Heavy
Food
28.1%​
29.1%​
23.9%​
21.7%​
3.6%​
3.5%​
12.1%​
10.9%​
6.8%​
6.2%​
28.8%​
29.0%​
-22.9%​
-33.7%​
NAAsset Heavy
Chemicals
134.7%​
97.9%​
16.4%​
36.3%​
1.1%​
2.7%​
2.8%​
6.8%​
2.5%​
6.9%​
22.7%​
20.5%​
-52.4%​
-24.0%​
-8.6%​
Asset Heavy
Beverages
24.1%​
22.3%​
64.9%​
68.7%​
13.8%​
14.5%​
23.8%​
25.3%​
23.1%​
24.6%​
34.9%​
35.0%​
11.7%​
16.7%​
11.3%​
Asset Lite
Manufacturing
18.3%​
23.2%​
49.5%​
47.9%​
2.3%​
2.3%​
5.4%​
5.8%​
4.9%​
5.3%​
11.5%​
11.5%​
0.1%​
-2.5%​
-1.8%​
Asset Lite
Chemicals
32.3%​
19.4%​
65.1%​
65.1%​
1.1%​
0.7%​
12.5%​
12.5%​
13.3%​
13.3%​
15.5%​
14.0%​
NANANAAsset Lite
A&D
11.3%​
10.7%​
32.8%​
32.1%​
9.3%​
9.1%​
18.7%​
17.5%​
16.1%​
15.1%​
46.3%​
45.8%​
18.5%​
34.4%​
23.4%​
Asset Lite
Manufacturing
12.1%​
11.9%​
82.2%​
80.8%​
6.6%​
6.3%​
17.6%​
15.6%​
16.3%​
14.7%​
14.8%​
14.5%​
31.8%​
22.3%​
17.6%​
Asset Lite
Health Care Equipment
21.5%​
19.5%​
65.0%​
95.0%​
0.6%​
-3.0%​
9.2%​
-8.2%​
9.3%​
-8.6%​
5.0%​
0.8%​
75.5%​
47.7%​
33.3%​
Asset Heavy
Health Care Equipment
27.0%​
28.4%​
31.8%​
29.2%​
3.6%​
4.1%​
9.9%​
10.6%​
7.2%​
8.0%​
31.8%​
34.2%​
-8.7%​
-1.8%​
-0.1%​
Asset Heavy
Manufacturing
40.7%​
40.8%​
66.8%​
67.8%​
4.2%​
4.5%​
9.0%​
9.7%​
8.7%​
10.0%​
15.9%​
15.2%​
24.3%​
24.8%​
16.0%​
Asset Heavy
Health Care Equipment
16.8%​
18.9%​
66.5%​
66.6%​
10.1%​
10.3%​
19.2%​
22.4%​
17.1%​
20.2%​
28.9%​
28.8%​
51.1%​
30.9%​
19.8%​
Asset Lite
Chemicals
36.3%​
34.2%​
127.9%​
130.4%​
11.7%​
13.0%​
25.3%​
26.9%​
27.1%​
29.0%​
17.2%​
18.8%​
-36.2%​
-11.8%​
-4.5%​
Asset Lite
Chemicals
20.8%​
20.1%​
90.2%​
88.9%​
7.1%​
7.1%​
15.9%​
16.7%​
16.3%​
17.0%​
15.6%​
16.0%​
-11.3%​
-0.1%​
-1.6%​
Asset Lite
Food
15.4%​
13.7%​
121.9%​
133.2%​
9.9%​
10.8%​
20.0%​
20.7%​
17.7%​
18.4%​
14.1%​
14.0%​
1.7%​
8.6%​
10.9%​
Asset Lite
Food
15.7%​
14.7%​
134.0%​
149.4%​
6.9%​
7.0%​
14.1%​
13.3%​
12.3%​
11.8%​
10.0%​
9.4%​
-6.4%​
-0.8%​
11.1%​
Asset Lite
Manufacturing
9.8%​
9.6%​
48.9%​
59.6%​
6.4%​
7.8%​
14.1%​
14.9%​
12.6%​
14.0%​
22.5%​
22.9%​
-27.3%​
1.0%​
NAAsset Lite
Manufacturing
15.5%​
16.5%​
88.5%​
87.7%​
6.7%​
6.6%​
19.9%​
17.3%​
14.6%​
12.9%​
14.7%​
14.6%​
-4.1%​
1.7%​
4.4%​
Asset Lite
Manufacturing
18.9%​
19.6%​
119.0%​
120.0%​
8.0%​
8.0%​
17.4%​
17.7%​
18.0%​
18.4%​
13.1%​
13.2%​
-8.2%​
0.6%​
3.3%​
Asset Lite
Household Products
16.2%​
16.1%​
124.4%​
127.3%​
14.5%​
14.7%​
31.9%​
31.1%​
31.5%​
32.1%​
20.9%​
20.8%​
9.3%​
9.9%​
12.2%​
Asset Lite
Food
29.0%​
28.3%​
77.4%​
81.3%​
6.7%​
6.7%​
15.5%​
14.9%​
13.2%​
13.2%​
17.2%​
16.9%​
18.3%​
-2.6%​
3.1%​
Asset Heavy
Health Care Equipment
21.5%​
21.1%​
130.1%​
123.0%​
18.6%​
16.8%​
45.2%​
39.8%​
45.3%​
39.9%​
26.1%​
25.2%​
19.2%​
17.8%​
26.9%​
Asset Heavy
Manufacturing
13.8%​
13.7%​
84.2%​
90.1%​
7.5%​
7.3%​
15.4%​
15.9%​
14.1%​
15.0%​
16.5%​
15.6%​
-12.0%​
-0.4%​
6.7%​
Asset Lite
Food
16.4%​
15.9%​
152.2%​
156.4%​
2.6%​
2.6%​
7.3%​
7.1%​
6.0%​
6.0%​
4.2%​
4.2%​
-15.4%​
-5.0%​
-2.5%​
Asset Lite
Manufacturing
8.8%​
9.2%​
101.4%​
97.1%​
11.6%​
11.2%​
38.6%​
37.5%​
40.4%​
43.8%​
21.0%​
20.8%​
-8.9%​
3.1%​
9.8%​
Asset Lite
Manufacturing
12.3%​
12.3%​
54.5%​
55.5%​
7.7%​
8.0%​
17.9%​
17.6%​
13.8%​
13.8%​
25.9%​
26.3%​
-3.0%​
13.7%​
9.0%​
Asset Lite
Food
17.6%​
16.4%​
49.6%​
68.0%​
6.1%​
7.2%​
15.4%​
16.0%​
11.5%​
13.4%​
20.2%​
18.8%​
7.5%​
15.4%​
17.4%​
Asset Lite
Manufacturing
14.8%​
14.0%​
70.0%​
71.6%​
5.5%​
5.6%​
10.9%​
11.3%​
10.8%​
11.3%​
15.6%​
15.8%​
-6.0%​
0.0%​
6.9%​
Asset Lite
Chemicals
22.9%​
17.2%​
22.9%​
22.9%​
0.3%​
0.2%​
2.7%​
2.7%​
2.7%​
2.7%​
12.8%​
11.7%​
NANANAAsset Lite
Household Products
15.7%​
16.4%​
65.5%​
71.3%​
8.6%​
9.2%​
25.9%​
24.1%​
16.9%​
16.7%​
23.0%​
23.2%​
4.3%​
12.6%​
11.8%​
Asset Lite
Health Care Equipment
18.8%​
18.1%​
38.3%​
41.9%​
4.8%​
5.1%​
12.4%​
14.0%​
8.8%​
10.4%​
26.6%​
26.0%​
15.9%​
21.5%​
24.4%​
Asset Lite
Food
36.5%​
33.8%​
60.9%​
76.9%​
8.3%​
8.5%​
16.6%​
15.9%​
15.2%​
15.1%​
24.2%​
21.7%​
48.7%​
-2.1%​
5.2%​
Asset Heavy
Manufacturing
12.4%​
13.0%​
76.6%​
74.1%​
6.3%​
6.1%​
12.9%​
12.1%​
12.3%​
11.4%​
17.5%​
17.5%​
7.7%​
18.2%​
13.3%​
Asset Lite
Manufacturing
14.5%​
14.3%​
69.6%​
69.1%​
5.8%​
5.8%​
13.5%​
13.1%​
12.6%​
12.5%​
17.9%​
17.3%​
-3.3%​
16.3%​
17.7%​
Asset Lite
Manufacturing
60.9%​
62.3%​
66.8%​
65.4%​
4.2%​
4.0%​
8.2%​
7.2%​
10.7%​
10.6%​
15.8%​
15.5%​
-27.9%​
-10.5%​
-6.4%​
Asset Heavy
Food
22.0%​
23.0%​
65.6%​
62.7%​
6.9%​
5.4%​
13.2%​
10.0%​
11.9%​
9.2%​
19.9%​
17.1%​
21.0%​
-10.8%​
2.8%​
Asset Lite
A&D
8.6%​
8.7%​
65.6%​
66.8%​
9.3%​
9.2%​
17.0%​
15.7%​
14.5%​
13.9%​
25.5%​
24.8%​
-1.6%​
26.7%​
24.3%​
Asset Lite
Chemicals
29.9%​
27.8%​
47.8%​
60.5%​
6.0%​
7.7%​
10.1%​
14.0%​
9.1%​
13.4%​
22.0%​
22.4%​
-9.8%​
-1.8%​
0.8%​
Asset Lite
Manufacturing
49.6%​
69.7%​
39.4%​
36.4%​
-0.3%​
-0.6%​
-1.1%​
-3.7%​
-0.4%​
-1.5%​
3.3%​
1.5%​
-15.3%​
12.2%​
-0.2%​
Asset Heavy
Food
23.3%​
24.0%​
47.4%​
45.0%​
4.9%​
4.8%​
11.9%​
10.8%​
8.0%​
7.3%​
22.2%​
21.6%​
5.9%​
-6.2%​
1.6%​
Asset Lite
A&D
17.2%​
16.3%​
71.0%​
75.0%​
7.1%​
6.8%​
13.0%​
12.1%​
12.5%​
11.8%​
18.8%​
18.0%​
31.5%​
32.3%​
24.8%​
Asset Lite
Health Care Equipment
14.8%​
15.5%​
45.0%​
41.8%​
4.8%​
4.6%​
13.8%​
12.9%​
9.5%​
8.8%​
31.9%​
33.6%​
-7.3%​
1.2%​
6.3%​
Asset Lite
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
Hey, you will want AVERAGEIFS for this, I have used a small subset of your data, expand and change the formulas as necessary:

Book1
ABCDEFGHIJKLMNOPQRSTUVW
1Sector3 Year Avg. PPE/Sales5 Year Avg. PPE/Sales3 Year Avg. Asset Turnover5 Year Avg. Asset Turnover3 Year Avg. ROA5 Year Avg. ROA3 Year Avg. ROCE5 Year Avg. ROCE3 Year Avg. ROIC5 Year Avg. ROIC3 Year Avg. EBITDA Margin5 Year Avg. EBITDA Margin1 Year TSR3 Year TSR5 Year TSRCategory1 Year TSR
2Household Products30.9%30.1%56.4%55.1%7.4%6.9%15.7%14.7%14.1%13.3%25.4%24.9%14.40%10.3%9.8%Asset HeavySector/CategoryAsset LiteAsset Heavy
3Beverages27.8%27.4%41.9%44.5%7.3%7.2%23.5%25.1%12.8%14.0%31.5%29.9%17.00%10.8%8.8%Asset LiteHousehold Products0.00%14.40%
4Beverages28.5%27.5%82.8%84.9%8.5%8.7%28.1%27.2%23.0%22.6%19.5%19.5%14.50%9.0%9.8%Asset LiteBeverages15.75%0.00%
5A&D14.6%14.0%75.7%86.6%3.9%4.2%26.3%25.2%229.7%208.2%9.0%9.2%-48.40%4.2%7.2%Asset LiteA&D-48.40%0.00%
6Health Care Equipment0.00%0.00%
7Manufacturing0.00%0.00%
8Food0.00%0.00%
9Chemicals0.00%0.00%
Sheet1
Cell Formulas
RangeFormula
V3:W9V3=IFERROR(AVERAGEIFS($N$2:$N$5,$A$2:$A$5,$U3,$Q$2:$Q$5,V$2),0)
 

Junaid Azhar

New Member
Joined
Mar 30, 2020
Messages
4
Office Version
  1. 2010
Platform
  1. Windows
Hi thanks for reply .. u select the column in hard number which is N2:N5 but i want it dynamic as per Column header.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
OK if column header is in cell V1 then try this:

Book1
ABCDEFGHIJKLMNOPQRSTUVW
1Sector3 Year Avg. PPE/Sales5 Year Avg. PPE/Sales3 Year Avg. Asset Turnover5 Year Avg. Asset Turnover3 Year Avg. ROA5 Year Avg. ROA3 Year Avg. ROCE5 Year Avg. ROCE3 Year Avg. ROIC5 Year Avg. ROIC3 Year Avg. EBITDA Margin5 Year Avg. EBITDA Margin1 Year TSR3 Year TSR5 Year TSRCategory1 Year TSR
2Household Products0.3090.3010.5640.5510.0740.0690.1570.1470.1410.1330.2540.2490.1440.1030.098Asset HeavySector/CategoryAsset LiteAsset Heavy
3Beverages0.2780.2740.4190.4450.0730.0720.2350.2510.1280.140.3150.2990.170.1080.088Asset LiteHousehold Products0.00%14.40%
4Beverages0.2850.2750.8280.8490.0850.0870.2810.2720.230.2260.1950.1950.1450.090.098Asset LiteBeverages15.75%0.00%
5A&D0.1460.140.7570.8660.0390.0420.2630.2522.2972.0820.090.092-0.4840.0420.072Asset LiteA&D-48.40%0.00%
6Health Care Equipment0.00%0.00%
7Manufacturing0.00%0.00%
8Food0.00%0.00%
9Chemicals0.00%0.00%
Sheet1
Cell Formulas
RangeFormula
V3:W9V3=IFERROR(AVERAGEIFS(INDIRECT(ADDRESS(2,MATCH($V$1,$A$1:$Q$1,0))&":"&ADDRESS(5,MATCH($V$1,$A$1:$Q$1,0))),$A$2:$A$5,$U3,$Q$2:$Q$5,V$2),0)


and if we were to change column header to 5 Year TSR then this happens:

Book1
ABCDEFGHIJKLMNOPQRSTUVW
1Sector3 Year Avg. PPE/Sales5 Year Avg. PPE/Sales3 Year Avg. Asset Turnover5 Year Avg. Asset Turnover3 Year Avg. ROA5 Year Avg. ROA3 Year Avg. ROCE5 Year Avg. ROCE3 Year Avg. ROIC5 Year Avg. ROIC3 Year Avg. EBITDA Margin5 Year Avg. EBITDA Margin1 Year TSR3 Year TSR5 Year TSRCategory5 Year TSR
2Household Products0.3090.3010.5640.5510.0740.0690.1570.1470.1410.1330.2540.2490.1440.1030.098Asset HeavySector/CategoryAsset LiteAsset Heavy
3Beverages0.2780.2740.4190.4450.0730.0720.2350.2510.1280.140.3150.2990.170.1080.088Asset LiteHousehold Products0.00%9.80%
4Beverages0.2850.2750.8280.8490.0850.0870.2810.2720.230.2260.1950.1950.1450.090.098Asset LiteBeverages9.30%0.00%
5A&D0.1460.140.7570.8660.0390.0420.2630.2522.2972.0820.090.092-0.4840.0420.072Asset LiteA&D7.20%0.00%
6Health Care Equipment0.00%0.00%
7Manufacturing0.00%0.00%
8Food0.00%0.00%
9Chemicals0.00%0.00%
Sheet1
Cell Formulas
RangeFormula
V3:W9V3=IFERROR(AVERAGEIFS(INDIRECT(ADDRESS(2,MATCH($V$1,$A$1:$Q$1,0))&":"&ADDRESS(5,MATCH($V$1,$A$1:$Q$1,0))),$A$2:$A$5,$U3,$Q$2:$Q$5,V$2),0)


So now the averageifs is dynamic to what header goes in to cell V1.

Note that I have used row numbers as 2 and 5 in the ADDRESS function as I am taking a subset of your data, you will need to change the 5 to your last row number.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,457
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It can be done without the volatile function INDIRECT.
I am also assuming that the formula only needs to look at columns N:P for the correct heading.

20 03 30.xlsm
ANOPQRSTUVW
1Sector1 Year TSR3 Year TSR5 Year TSRCategory1 Year TSR
2Household Products14.40%10.30%9.80%Asset HeavySector/CategoryAsset LiteAsset Heavy
3Beverages17.00%10.80%8.80%Asset LiteHousehold Products0.00%14.40%
4Beverages14.50%9.00%9.80%Asset LiteBeverages15.75%0.00%
5A&D-48.40%4.20%7.20%Asset LiteA&D-48.40%0.00%
6Health Care Equipment0.00%0.00%
7Manufacturing0.00%0.00%
8Food0.00%0.00%
9Chemicals0.00%0.00%
AverageIfs
Cell Formulas
RangeFormula
V3:W9V3=IFERROR(AVERAGEIFS(INDEX($N$2:$P$5,0,MATCH($V$1,$N$1:$P$1,0)),$A$2:$A$5,$U3,$Q$2:$Q$5,V$2),0)



@Junaid Azhar
I suggest that you try to keep your sample data a bit smaller & also use XL2BB to display it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Was averageifs available in 2010 :unsure: ?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,457
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Was averageifs available in 2010 :unsure: ?
I had my doubts too, but according to this it did. (I don't trust the Microsoft formula help for version info as I have found it to be wrong for quite a few functions :()
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
Hopefully it is available, I guess we will wait and see from the OP!

Also Peter, in your formula I assume by putting the row number as a 0 for the index creates a spill range? I don't think I've seen this before so I was curious and tested it, now as spill ranges are new to O365 will Excel 2010 have the power to run this formula? Maybe it gets CSE? I am not sure just curious!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
You're quite right, I keep forgetting I've got an "Excel Functions Bible Menu" sheet that I got from somewhere (possibly from Ron de Bruin's site) & that shows it as new in 2007.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,457
Office Version
  1. 365
Platform
  1. Windows
I assume by putting the row number as a 0 for the index creates a spill range
No, it is not related to a spill range & will work fine in Excel 2010. I have used it as the row reference in an INDEX function which means "use all rows"
Example =SUM(INDEX(A1:D10,0,2))
means sum all rows, column 2 or SUM(B1:B10)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,279
Messages
5,635,285
Members
416,851
Latest member
zeldadav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top