How to average subtotal cells in a column with other data

[rvissw]

I want to average all the "Total" "ABS Qty" only. The "Total" quantity is a
subtotal formula. In other words, (14+2+2+16+10)/5=8.8

ABS Qty Week
2 23
2 23
2 23
2 23
6 23
14 23Total
2 24
2 24Total
2 25
2 25Total
2 26
8 26
2 26
2 26
2 26
16 26Total
2 28
2 28
2 28
2 28
2 28
10 28 Total

 

[Jim Cone]

With your data in B6:B27...
=(SUM(B6:B27)-SUBTOTAL(9,B6:B27))/(COUNTA(B6:B27)-SUBTOTAL(3,B6:B27))
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"rvissw"
wrote in message
I want to average all the "Total" "ABS Qty" only. The "Total" quantity is a
subtotal formula. In other words, (14+2+2+16+10)/5=8.8

ABS Qty Week
2 23
2 23
2 23
2 23
6 23
14 23Total
2 24
2 24Total
2 25
2 25Total
2 26
8 26
2 26
2 26
2 26
16 26Total
2 28
2 28
2 28
2 28
2 28
10 28 Total

 

[ShaneDevenshire]

Hi,

If you are using 2007 then here is the formula:

=AVERAGEIF(B2:B23,"*Total",A2:A23)

This assumes that the numeric data is in column A and the 28Total stuff is
in column B.

If you are using 2003 this formula will work:

=AVERAGE(IF(RIGHT(B2:B23,5)="Total",A2:A23,""))

This second formula is an array entered formula which means you press Shift
Ctrl Enter instead of Enter when you type it in.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver to help
find life beyond earth.

 

[rvissw]

Could you show me how to write a formula for STDEV & MEDIAN?

 

[rvissw]

It worked brillantly!! Thank you! Thank you! I knew there had to be a way to
do it.

 

[David Biddulph]

Excel help can show you.
--
David Biddulph

Could you show me how to write a formula for STDEV & MEDIAN?

....

 

[rvissw]

Can you show me how to do it for STDEV & MEDIAN

 

[Jim Cone]

Cell G1 has "Total" in it.
Your data is in columns B:C, between rows 1:100

=INDEX($B$1:$C$100,SMALL(IF(RIGHT($C$1:$C$100,5)=$G$1,ROW($C$1:$C$100)),ROW(1:1)),1)

Entered as an array formula (Ctrl+Shift+Enter) and filled down until you get an error value.
Use the Median and Stdev functions on the returned values.

In five consecutive cells, I get...
14
2
2
16
10
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"rvissw" <[email protected]>
wrote in message
Can you show me how to do it for STDEV & MEDIAN

 

[Jim Cone]

I just took a look at what Shane Devenshire posted.
His method is a far better solution.
It only requires one formula and you can use it for average, median and stdev.
(I still have and refer to the book he helped author in 1994 - "Excel Professional Techniques")

 

[rvissw]

Thank you! You people are awesome!!!!!!

Hi,

If you are using 2007 then here is the formula:

=AVERAGEIF(B2:B23,"*Total",A2:A23)

This assumes that the numeric data is in column A and the 28Total stuff is
in column B.

If you are using 2003 this formula will work:

=AVERAGE(IF(RIGHT(B2:B23,5)="Total",A2:A23,""))

This second formula is an array entered formula which means you press Shift
Ctrl Enter instead of Enter when you type it in.

--
Cheers,
Shane Devenshire
Microsoft Excel MVP
Join http://setiathome.berkeley.edu/ and download a free screensaver to help
find life beyond earth.

 

[randy aitken]

What if this were a pivot table and there were one more row of data.

For instance, I have customer, order#, and item# in my row fields, and sum of total in my data field.

I have subtotaled on order# such that I have a total for each order.

Now I wish to find the average total per order by customer.

When I set the average on the customer field it gives me average of total, ie, the average price per item. But I am seeking instead to average my subtotal.

(Excel 03)

 

[randy aitken]

What if this were a pivot table and there were one more row of data.

For instance, I have customer, order#, and item# in my row fields, and sum of total in my data field.

I have subtotaled on order# such that I have a total for each order.

Now I wish to find the average total per order by customer.

When I set the average on the customer field it gives me average of total, ie, the average price per item. But I am seeking instead to average my subtotal.

(Excel 03)