Average with certain conditions

[Junaid Azhar]

Would like to average with certain condition e.g. Sector/Category , Asset lite & 1 Year TSR from below mentioned table.

	1 Year TSR
Sector/Category	Asset Lite	Asset Heavy
Household Products
Beverages
A&D
Health Care Equipment
Manufacturing
Food
Chemicals

Sector

3 Year Avg. PPE/Sales

5 Year Avg. PPE/Sales

3 Year Avg. Asset Turnover

5 Year Avg. Asset Turnover

3 Year Avg. ROA

5 Year Avg. ROA

3 Year Avg. ROCE

5 Year Avg. ROCE

3 Year Avg. ROIC

5 Year Avg. ROIC

3 Year Avg. EBITDA Margin

5 Year Avg. EBITDA Margin

1 Year TSR

3 Year TSR

5 Year TSR

Category

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%​

NA

NA

Asset 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%​

NA

Asset 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%​

NA

NA

NA

Asset 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%​

NA

Asset 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%​

NA

NA

NA

Asset 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

 

[tyija1995]

Hey, you will want AVERAGEIFS for this, I have used a small subset of your data, expand and change the formulas as necessary:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Sector	3 Year Avg. PPE/Sales	5 Year Avg. PPE/Sales	3 Year Avg. Asset Turnover	5 Year Avg. Asset Turnover	3 Year Avg. ROA	5 Year Avg. ROA	3 Year Avg. ROCE	5 Year Avg. ROCE	3 Year Avg. ROIC	5 Year Avg. ROIC	3 Year Avg. EBITDA Margin	5 Year Avg. EBITDA Margin	1 Year TSR	3 Year TSR	5 Year TSR	Category					1 Year TSR
2	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.40%	10.3%	9.8%	Asset Heavy				Sector/Category	Asset Lite	Asset Heavy
3	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.00%	10.8%	8.8%	Asset Lite				Household Products	0.00%	14.40%
4	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.50%	9.0%	9.8%	Asset Lite				Beverages	15.75%	0.00%
5	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.40%	4.2%	7.2%	Asset Lite				A&D	-48.40%	0.00%
6																					Health Care Equipment	0.00%	0.00%
7																					Manufacturing	0.00%	0.00%
8																					Food	0.00%	0.00%
9																					Chemicals	0.00%	0.00%
Sheet1

Cell Formulas
Range	Formula
V3:W9	V3	=IFERROR(AVERAGEIFS($N$2:$N$5,$A$2:$A$5,$U3,$Q$2:$Q$5,V$2),0)

 

[Junaid Azhar]

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]

OK if column header is in cell V1 then try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Sector	3 Year Avg. PPE/Sales	5 Year Avg. PPE/Sales	3 Year Avg. Asset Turnover	5 Year Avg. Asset Turnover	3 Year Avg. ROA	5 Year Avg. ROA	3 Year Avg. ROCE	5 Year Avg. ROCE	3 Year Avg. ROIC	5 Year Avg. ROIC	3 Year Avg. EBITDA Margin	5 Year Avg. EBITDA Margin	1 Year TSR	3 Year TSR	5 Year TSR	Category					1 Year TSR
2	Household Products	0.309	0.301	0.564	0.551	0.074	0.069	0.157	0.147	0.141	0.133	0.254	0.249	0.144	0.103	0.098	Asset Heavy				Sector/Category	Asset Lite	Asset Heavy
3	Beverages	0.278	0.274	0.419	0.445	0.073	0.072	0.235	0.251	0.128	0.14	0.315	0.299	0.17	0.108	0.088	Asset Lite				Household Products	0.00%	14.40%
4	Beverages	0.285	0.275	0.828	0.849	0.085	0.087	0.281	0.272	0.23	0.226	0.195	0.195	0.145	0.09	0.098	Asset Lite				Beverages	15.75%	0.00%
5	A&D	0.146	0.14	0.757	0.866	0.039	0.042	0.263	0.252	2.297	2.082	0.09	0.092	-0.484	0.042	0.072	Asset Lite				A&D	-48.40%	0.00%
6																					Health Care Equipment	0.00%	0.00%
7																					Manufacturing	0.00%	0.00%
8																					Food	0.00%	0.00%
9																					Chemicals	0.00%	0.00%
Sheet1

Cell Formulas
Range	Formula
V3:W9	V3	=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Sector	3 Year Avg. PPE/Sales	5 Year Avg. PPE/Sales	3 Year Avg. Asset Turnover	5 Year Avg. Asset Turnover	3 Year Avg. ROA	5 Year Avg. ROA	3 Year Avg. ROCE	5 Year Avg. ROCE	3 Year Avg. ROIC	5 Year Avg. ROIC	3 Year Avg. EBITDA Margin	5 Year Avg. EBITDA Margin	1 Year TSR	3 Year TSR	5 Year TSR	Category					5 Year TSR
2	Household Products	0.309	0.301	0.564	0.551	0.074	0.069	0.157	0.147	0.141	0.133	0.254	0.249	0.144	0.103	0.098	Asset Heavy				Sector/Category	Asset Lite	Asset Heavy
3	Beverages	0.278	0.274	0.419	0.445	0.073	0.072	0.235	0.251	0.128	0.14	0.315	0.299	0.17	0.108	0.088	Asset Lite				Household Products	0.00%	9.80%
4	Beverages	0.285	0.275	0.828	0.849	0.085	0.087	0.281	0.272	0.23	0.226	0.195	0.195	0.145	0.09	0.098	Asset Lite				Beverages	9.30%	0.00%
5	A&D	0.146	0.14	0.757	0.866	0.039	0.042	0.263	0.252	2.297	2.082	0.09	0.092	-0.484	0.042	0.072	Asset Lite				A&D	7.20%	0.00%
6																					Health Care Equipment	0.00%	0.00%
7																					Manufacturing	0.00%	0.00%
8																					Food	0.00%	0.00%
9																					Chemicals	0.00%	0.00%
Sheet1

Cell Formulas
Range	Formula
V3:W9	V3	=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.

 

[Peter_SSs]

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
	A													N	O	P	Q	R	S	T	U	V	W
1	Sector													1 Year TSR	3 Year TSR	5 Year TSR	Category					1 Year TSR
2	Household Products													14.40%	10.30%	9.80%	Asset Heavy				Sector/Category	Asset Lite	Asset Heavy
3	Beverages													17.00%	10.80%	8.80%	Asset Lite				Household Products	0.00%	14.40%
4	Beverages													14.50%	9.00%	9.80%	Asset Lite				Beverages	15.75%	0.00%
5	A&D													-48.40%	4.20%	7.20%	Asset Lite				A&D	-48.40%	0.00%
6																					Health Care Equipment	0.00%	0.00%
7																					Manufacturing	0.00%	0.00%
8																					Food	0.00%	0.00%
9																					Chemicals	0.00%	0.00%
AverageIfs

Cell Formulas
Range	Formula
V3:W9	V3	=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]

Was averageifs available in 2010 ?

 

[Peter_SSs]

Was averageifs available in 2010 ?

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]

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]

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]

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)