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]

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)

OK great thanks for clearing that up Peter, makes sense