Averageifs where subgroups are used

G

Go Bucks!!!

I want to average the travels and works by subgroup.

My data looks like this.

Subgroup Activity Time (minutes)
3641100-1 WORK 30
3641100-2 WORK 60
3641100-2 TRAVEL 60
3641100-2 TRAVEL 30
3641100-3 TRAVEL 120
3619040-1 WORK 60
3619040-1 TRAVEL 30

Avg travel min per sub 65.0
=average of 45,120, and 30
Avg work min per sub 50.0
=average of 30,60, and 60

There will be multiple travels and works per subgroup (as with sub 3641100-2)

Thanks
 
G

Go Bucks!!!

Opps I screwed up. Total travel time should be 80

=average of 90,120, and 30

Subgroup 3641100-2 had travel time of 60+30.
 
A

Ashish Mathur

Hi,

Assuming your data is in range B4;D11 (including the headers), type travel
in cell C14 and Work in cell C15. Then array enter (Ctrl+Shift+Enter) the
following formula in cell D14 and copy down

=SUMIF($C$5:$C$11,C14,$D$5:$D$11)/SUMPRODUCT(1*(FREQUENCY(IF($C$5:$C$11=$C14,MATCH($B$5:$B$11,$B$5:$B$11,0)),ROW($C$5:$C$11)-ROW($C$4))>0))

Hope this helps



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average problem 1
Summing values within a matrix who are both in a column where theheaders are within a certain numeri 4
Consolidate Excel Data from multiple sheets into one 1
How do I calculate a weighted average and exclude the empthy cells 2
Statistical functions on non-consequtive columns 7
vlook up (average) 2
Using drop-down lists 4
I need formula help 1

Top