A B
1 January
2 Clothing & Apparel 44,408
3 Shoes 23,693
4 Jewellery 6,068
5 Cosmetics 29,670
6 Women's Fashions 103,839 (Subtotal)
7
8 Clothing & Apparel 28,600
9 Shoes 21,280
10 Men's Fashions 49,880 (Subtotal)
11
12 TOTAL 153,719
13 Average (Need a Formula on cell: B13 for the two Department)
I am grateful to anyone who can find a Formula for the Excel Worksheet.
THANKS.

Amy,

SUBTOTAL can perform functions other than totalling, such as averaging, and
SUBTOTAL fucntions ignore other SUBTOTAL functions. The first number tells
SUBTOTAL which function to use; 9 for sum and 1 for average.

B6: =SUBTOTAL(9,B2:B5)
B10: =SUBTOTAL(9,B8:B9)
B12: =SUBTOTAL(9,B2:B10)
B13: =SUBTOTAL(1,B2:B10)

Tim C

"Amy Yeh" <Amy wrote:

A B
1 January
2 Clothing & Apparel 44,408
3 Shoes 23,693
4 Jewellery 6,068
5 Cosmetics 29,670
6 Women's Fashions 103,839 (Subtotal)
7
8 Clothing & Apparel 28,600
9 Shoes 21,280
10 Men's Fashions 49,880 (Subtotal)
11
12 TOTAL 153,719
13 Average (Need a Formula on cell: B13 for the two
Department)
I am grateful to anyone who can find a Formula for the Excel Worksheet.
THANKS.

Tim,

Thanks for your solution. I had used your formula on cell: B13, and the
answer was 25,619.83. Then I used a calculator to find out the correct
answer, which is 76,859.5. 153,719 is the Total Sales of the two
Departments, so it should be divided by 2. Therefore, I'm still trying to
figure out the formula for cell: B13.

"Tim C" wrote:

Amy,

SUBTOTAL can perform functions other than totalling, such as averaging, and
SUBTOTAL fucntions ignore other SUBTOTAL functions. The first number tells
SUBTOTAL which function to use; 9 for sum and 1 for average.

B6: =SUBTOTAL(9,B2:B5)
B10: =SUBTOTAL(9,B8:B9)
B12: =SUBTOTAL(9,B2:B10)
B13: =SUBTOTAL(1,B2:B10)

Tim C

"Amy Yeh" <Amy wrote:

A B
1 January
2 Clothing & Apparel 44,408
3 Shoes 23,693
4 Jewellery 6,068
5 Cosmetics 29,670
6 Women's Fashions 103,839 (Subtotal)
7
8 Clothing & Apparel 28,600
9 Shoes 21,280
10 Men's Fashions 49,880 (Subtotal)
11
12 TOTAL 153,719
13 Average (Need a Formula on cell: B13 for the two
Department)
I am grateful to anyone who can find a Formula for the Excel Worksheet.
THANKS.

Amy,

Sorry, I thought you wanted an average of subdepartments. How about:

=B12/2

Tim C

"Amy Yeh" <Amy wrote in message
...
Tim,

Thanks for your solution. I had used your formula on cell: B13, and the
answer was 25,619.83. Then I used a calculator to find out the correct
answer, which is 76,859.5. 153,719 is the Total Sales of the two
Departments, so it should be divided by 2. Therefore, I'm still trying
to
figure out the formula for cell: B13.

"Tim C" wrote:

Amy,

SUBTOTAL can perform functions other than totalling, such as averaging,
and
SUBTOTAL fucntions ignore other SUBTOTAL functions. The first number
tells
SUBTOTAL which function to use; 9 for sum and 1 for average.

B6: =SUBTOTAL(9,B2:B5)
B10: =SUBTOTAL(9,B8:B9)
B12: =SUBTOTAL(9,B2:B10)
B13: =SUBTOTAL(1,B2:B10)

Tim C

"Amy Yeh" <Amy wrote:

A B
1 January
2 Clothing & Apparel 44,408
3 Shoes 23,693
4 Jewellery 6,068
5 Cosmetics 29,670
6 Women's Fashions 103,839 (Subtotal)
7
8 Clothing & Apparel 28,600
9 Shoes 21,280
10 Men's Fashions 49,880 (Subtotal)
11
12 TOTAL 153,719
13 Average (Need a Formula on cell: B13 for the two
Department)
I am grateful to anyone who can find a Formula for the Excel
Worksheet.
THANKS.

Tim:

I'm not sure about your last solution. Thanks for taking your time to
reply my question.

"Tim C" wrote:

Amy,

Sorry, I thought you wanted an average of subdepartments. How about:

=B12/2

Tim C

"Amy Yeh" <Amy wrote in message
...
Tim,

Thanks for your solution. I had used your formula on cell: B13, and the
answer was 25,619.83. Then I used a calculator to find out the correct
answer, which is 76,859.5. 153,719 is the Total Sales of the two
Departments, so it should be divided by 2. Therefore, I'm still trying
to
figure out the formula for cell: B13.

"Tim C" wrote:

Amy,

SUBTOTAL can perform functions other than totalling, such as averaging,
and
SUBTOTAL fucntions ignore other SUBTOTAL functions. The first number
tells
SUBTOTAL which function to use; 9 for sum and 1 for average.

B6: =SUBTOTAL(9,B2:B5)
B10: =SUBTOTAL(9,B8:B9)
B12: =SUBTOTAL(9,B2:B10)
B13: =SUBTOTAL(1,B2:B10)

Tim C

"Amy Yeh" <Amy wrote:

A B
1 January
2 Clothing & Apparel 44,408
3 Shoes 23,693
4 Jewellery 6,068
5 Cosmetics 29,670
6 Women's Fashions 103,839 (Subtotal)
7
8 Clothing & Apparel 28,600
9 Shoes 21,280
10 Men's Fashions 49,880 (Subtotal)
11
12 TOTAL 153,719
13 Average (Need a Formula on cell: B13 for the two
Department)
I am grateful to anyone who can find a Formula for the Excel
Worksheet.
THANKS.