Home Menu

Menu


ExcelBanter » Excel Newsgroups » Charts and Charting in Excel > How do I get averages based on specific fields.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 5
Default How do I get averages based on specific fields.
Given data such as below,
Is there and easy way to find and display in a single cell:
what is the average time (Hours:mins) for all tickets of Complete
status.
and
what is the average Hours:mins for all tickets of status=picked AND of
the type FWSnap, ?

Thanks,
Crzzy1

ticket Date Time Status TYPE
Hours:mins
44738 11/2/2008 4:45 Opened
FWRules 0:00
44738 11/2/2008 6:41 Picked
FWRules 1:56
44738 11/2/2008 7:14 Approved
FWRules 2:29
44738 11/3/2008 0:46 Complete FWRules
17:32
44739 11/3/2008 2:43 Opened
FWSnap 0:00
44739 11/3/2008 3:15 Picked
FWSnap 0:32
44739 11/3/2008 3:20 Approved
FWSnap 0:37
44739 11/3/2008 8:25 Complete
FWSnap 5:42
44740 11/3/2008 6:38 Opened
OtherRout 0:00
44740 11/3/2008 7:27 Picked
OtherRout 0:49
44740 11/3/2008 7:28 Approved
OtherRout 0:50
44740 11/3/2008 7:40 Complete
OtherRout 1:02
44741 11/3/2008 9:27 Opened
FWUser 0:00
44741 11/3/2008 9:28 Picked
FWUser 0:01
44741 11/3/2008 9:28 Approved
FWUser 0:01
44741 11/3/2008 10:17 Complete
FWUser 0:50
44742 11/3/2008 9:35 Opened
OtherRout 0:00
44742 11/3/2008 9:36 Picked
OtherRout 0:01
44742 11/3/2008 10:04 Approved
OtherRout 0:29
44742 11/3/2008 10:13 Complete
OtherRout 0:38
44743 11/3/2008 9:59 Opened
FWRules 0:00
44743 11/3/2008 10:04 Picked
FWRules 0:05
44743 11/3/2008 10:04 Approved
FWRules 0:05
44743 11/3/2008 10:11 Complete
FWRules 0:12
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 4,393
Default How do I get averages based on specific fields.
If column D has Status, E and Type and C has times
=SUMPRODUCT(--(D1:D100="picked"), --(E1:E100="FWSnap"), C1:C100)
Adjust ranges but do not use full-column references (D:D) unless you have
Excel 2007
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Given data such as below,
Is there and easy way to find and display in a single cell:
what is the average time (Hours:mins) for all tickets of Complete
status.
and
what is the average Hours:mins for all tickets of status=picked AND of
the type FWSnap, ?

Thanks,
Crzzy1

ticket Date Time Status TYPE
Hours:mins
44738 11/2/2008 4:45 Opened
FWRules 0:00
44738 11/2/2008 6:41 Picked
FWRules 1:56
44738 11/2/2008 7:14 Approved
FWRules 2:29
44738 11/3/2008 0:46 Complete FWRules
17:32
44739 11/3/2008 2:43 Opened
FWSnap 0:00
44739 11/3/2008 3:15 Picked
FWSnap 0:32
44739 11/3/2008 3:20 Approved
FWSnap 0:37
44739 11/3/2008 8:25 Complete
FWSnap 5:42
44740 11/3/2008 6:38 Opened
OtherRout 0:00
44740 11/3/2008 7:27 Picked
OtherRout 0:49
44740 11/3/2008 7:28 Approved
OtherRout 0:50
44740 11/3/2008 7:40 Complete
OtherRout 1:02
44741 11/3/2008 9:27 Opened
FWUser 0:00
44741 11/3/2008 9:28 Picked
FWUser 0:01
44741 11/3/2008 9:28 Approved
FWUser 0:01
44741 11/3/2008 10:17 Complete
FWUser 0:50
44742 11/3/2008 9:35 Opened
OtherRout 0:00
44742 11/3/2008 9:36 Picked
OtherRout 0:01
44742 11/3/2008 10:04 Approved
OtherRout 0:29
44742 11/3/2008 10:13 Complete
OtherRout 0:38
44743 11/3/2008 9:59 Opened
FWRules 0:00
44743 11/3/2008 10:04 Picked
FWRules 0:05
44743 11/3/2008 10:04 Approved
FWRules 0:05
44743 11/3/2008 10:11 Complete
FWRules 0:12


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
  
Posts: 4,393
Default How do I get averages based on specific fields.
Oh dear, that was half-done. It gives the sum, you want average
=SUMPRODUCT(--(D1:D100="picked"), --(E1:E100="FWSnap"),
C1:C100)/=SUMPRODUCT(--(D1:D100="picked"), --(E1:E100="FWSnap"))
But if you have Excel 2007, look at AVERAGEIFS
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
If column D has Status, E and Type and C has times
=SUMPRODUCT(--(D1:D100="picked"), --(E1:E100="FWSnap"), C1:C100)
Adjust ranges but do not use full-column references (D:D) unless you have
Excel 2007
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Given data such as below,
Is there and easy way to find and display in a single cell:
what is the average time (Hours:mins) for all tickets of Complete
status.
and
what is the average Hours:mins for all tickets of status=picked AND of
the type FWSnap, ?

Thanks,
Crzzy1

ticket Date Time Status TYPE
Hours:mins
44738 11/2/2008 4:45 Opened
FWRules 0:00
44738 11/2/2008 6:41 Picked
FWRules 1:56
44738 11/2/2008 7:14 Approved
FWRules 2:29
44738 11/3/2008 0:46 Complete FWRules
17:32
44739 11/3/2008 2:43 Opened
FWSnap 0:00
44739 11/3/2008 3:15 Picked
FWSnap 0:32
44739 11/3/2008 3:20 Approved
FWSnap 0:37
44739 11/3/2008 8:25 Complete
FWSnap 5:42
44740 11/3/2008 6:38 Opened
OtherRout 0:00
44740 11/3/2008 7:27 Picked
OtherRout 0:49
44740 11/3/2008 7:28 Approved
OtherRout 0:50
44740 11/3/2008 7:40 Complete
OtherRout 1:02
44741 11/3/2008 9:27 Opened
FWUser 0:00
44741 11/3/2008 9:28 Picked
FWUser 0:01
44741 11/3/2008 9:28 Approved
FWUser 0:01
44741 11/3/2008 10:17 Complete
FWUser 0:50
44742 11/3/2008 9:35 Opened
OtherRout 0:00
44742 11/3/2008 9:36 Picked
OtherRout 0:01
44742 11/3/2008 10:04 Approved
OtherRout 0:29
44742 11/3/2008 10:13 Complete
OtherRout 0:38
44743 11/3/2008 9:59 Opened
FWRules 0:00
44743 11/3/2008 10:04 Picked
FWRules 0:05
44743 11/3/2008 10:04 Approved
FWRules 0:05
44743 11/3/2008 10:11 Complete
FWRules 0:12




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
need to move cursor to specific fields Cindy Excel Worksheet Functions 1 January 17th 08 11:59 PM
How do I pull a specific name based on a specific value from mult. rpctoo Excel Worksheet Functions 1 August 10th 07 04:12 PM
Creating a baseball lineup based on averages and player preferences. Jeremy Gollehon Excel Worksheet Functions 0 February 22nd 07 11:46 PM
Averages based on dates PA Excel Worksheet Functions 6 April 11th 06 04:37 AM
Add spaces for specific fields to paste in Corey Excel Discussion (Misc queries) 3 December 30th 05 10:22 PM


All times are GMT +1. The time now is 07:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement