Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-26-2019, 09:51 AM
Grido Grido is offline Average Processing Time Removing Outliers Windows 10 Average Processing Time Removing Outliers Office 2013
Novice
Average Processing Time Removing Outliers
 
Join Date: Jun 2019
Posts: 2
Grido is on a distinguished road
Default Average Processing Time Removing Outliers

I was hoping someone would be able to help, most of the time I can find a solution to my Excel queries by a quick Google, but no luck this time.

I have a series of data such as:
Item 1 -- 03:42
Item 2 -- 02:20
Item 1 -- 00:02
Item 2 -- 01:50
Item 2 -- 57:02
Item 1 -- 03:30
but a lot more lines (about 30k), and a lot more 'Item' types (about 160).

The corresponding value is the amount of time it takes to action said item. With each line being a different time that same item type has been actioned.

I am looking to get an average time for say specifically Item 1, but removing any outliers.

I currently have:
PHP Code:
=IFERROR(AVERAGEIFS(InputData!G:G,InputData!$B:$B,$B18),""
with column G being the time taken for each entry, column B the item type, and B18 being the particular item type I'm looking for on another tab.


Obviously this method does not remove outliers however.


I've looked at TRIMMEAN, however as commented in other places, this tends to rather arbitrary culling of data
PHP Code:
{=TRIMMEAN(IF(InputData!B:B=B18,InputData!G1:G36305),5%)} 
Also looked at using Quartiles, however when doing it, the Lower end is below 00:00:00, and so produces an error. I've amended it and IFERROR'd to 0, however I feel that skews the data in a way I shouldn't
PHP Code:
25%ile - (IQR*1.5
Anyone able to suggest how I can do this?




Much appreciated if you can!
Reply With Quote
  #2  
Old 06-26-2019, 10:38 PM
ArviLaanemets ArviLaanemets is offline Average Processing Time Removing Outliers Windows 8 Average Processing Time Removing Outliers Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

You need a helper column with formula where an outlier returns 1, otherwise 0.
Reply With Quote
  #3  
Old 06-27-2019, 12:33 AM
Grido Grido is offline Average Processing Time Removing Outliers Windows 10 Average Processing Time Removing Outliers Office 2013
Novice
Average Processing Time Removing Outliers
 
Join Date: Jun 2019
Posts: 2
Grido is on a distinguished road
Default

Can you elaborate?


I'm not sure how that helps determine what data are outliers?


Thanks
Reply With Quote
  #4  
Old 06-27-2019, 03:39 AM
ArviLaanemets ArviLaanemets is offline Average Processing Time Removing Outliers Windows 8 Average Processing Time Removing Outliers Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

I didn't have a clue what are criterions for entry being outlier for you, so I simply made them up. And I didn't create additional data for example, so it is not very presentable. Anyway I think you can get the idea.
Attached Files
File Type: xlsx AvgExample.xlsx (12.4 KB, 6 views)
Reply With Quote
Reply

Tags
average if, outliers, time calculation

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average Processing Time Removing Outliers Average Processing Time christian.calimlim22 Excel 3 04-04-2017 11:25 PM
Average Processing Time Removing Outliers how to calculate average time aymanharake Excel 6 02-17-2017 04:22 PM
Calculating Average Time across days lizakay Excel 3 11-25-2015 05:16 PM
Average minus outliers? markg2 Excel 2 01-07-2012 05:46 PM
Average Processing Time Removing Outliers Processing Time Intervals pkrishna Excel 5 09-30-2011 06:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft