Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? .... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted Average Within Sort/Subtotal | Excel Discussion (Misc queries) | |||
Average of cells in a column | Excel Worksheet Functions | |||
SUBTOTAL function to average column without blanks | Excel Worksheet Functions | |||
how can I average more cells only in a column by a criteria? | Excel Worksheet Functions | |||
Average the Last Five Cells in a Column | Excel Worksheet Functions |