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!