How do I nesting subtotal function within average function in Exc

[Amy Yeh]

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 C]

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]

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]

Amy,

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

=B12/2

Tim C

 

[Amy Yeh]

Tim:

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