How to average subtotal cells in a column with other data
 

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

How to average subtotal cells in a column with other data
 

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

How to average subtotal cells in a column with other data
 

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" wrote:

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

How to average subtotal cells in a column with other data
 

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

"Jim Cone" wrote:


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

How to average subtotal cells in a column with other data
 

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

"Jim Cone" wrote:


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

How to average subtotal cells in a column with other data
 

Excel help can show you.
--
David Biddulph

"rvissw" wrote in message
...
Could you show me how to write a formula for STDEV & MEDIAN?
....

How to average subtotal cells in a column with other data
 

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

"Jim Cone" wrote:


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

How to average subtotal cells in a column with other data
 

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"
wrote in message
Can you show me how to do it for STDEV & MEDIAN



"Jim Cone" wrote:
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

How to average subtotal cells in a column with other data
 

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")
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

How to average subtotal cells in a column with other data
 

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

"ShaneDevenshire" wrote:

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" wrote:

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

Pivot Table help
 

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)

Average of Pivot Subtotal
 

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)