Averageifs where subgroups are used

  • Thread starter Thread starter Go Bucks!!!
  • Start date Start date
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
 
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.
 
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
 
Back
Top