Averageifs where subgroups are used
 

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

Averageifs where subgroups are used
 

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.

Averageifs where subgroups are used
 

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

"Go Bucks!!!" wrote in message
...
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

Averageifs where subgroups are used
 

Ashish works great! Thank you for responding so fast. That was a big help.





"Ashish Mathur" wrote:

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

"Go Bucks!!!" wrote in message
...
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

Averageifs where subgroups are used
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Go Bucks!!!" wrote in message
...
Ashish works great! Thank you for responding so fast. That was a big
help.





"Ashish Mathur" wrote:

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

"Go Bucks!!!" wrote in message
...
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