Average of minimum duplicate value.

[Philippe de Vlaminck]

Hello guys,
question regarding averages:

I am looking for a way to average the time in the columns with hours and that for each column. It may only select the lowest value of the lines with duplicates (since this time stamp is relevant).
Any ideas? Been trying with averageifs, min, ... but haven't found a decent solution yet.

Part of the file:


Thanks in advance,
Kind regards
Phil

 

[Andrew Poulsom]

To use any formulas on that data you need to fill in the blank cells in the first 3 columns. Here's how:

http://www.contextures.com/xlDataEntry02.html

 

[Philippe de Vlaminck]

Hey Andrew,

Thx for the reply, filling the data can be dont with the pivot table options, tried combinations with that but not luck yet

Grts,
Phil

 

[Philippe de Vlaminck]

done and no even **** my typing skills

 

[Andrew Poulsom]

You didn't say that your data was in a pivot table. Can't you change the Report Layout to Tabular Form or Repeat All Item Labels?

Welcome to MrExcel, by the way.

 

[Philippe de Vlaminck]

Hey Andrew,

I have tried repeating the labels but havent been able to figure out a formula that will average the last columns without taking the along the high values for the duplicates. The time stamps are trucks and each of em have their own line per parcel. The multi load trucks have 2 or more lines, but only the smallest time is relevant for that specific process step.

For instance, the first 2 lines:
Truck has 3.07 h pretime for the first parcel and 4.11 h for the 2nd. That is because the startpoint for the 2nd parcel is the endpoint of the last.

In the averages of the pivot table, all times are taken into account which makes the result wrong.

Hope this clarifies it abit.

And thanks for the welcome, heard about this site on the excel is fun channel on youtube where i am a longtime customer

Grts,
Phil

 

[Andrew Poulsom]

Please post a small but representative sample of your data. Don't post an image as I want to be able to copy the data and paste it into Excel.

 

[Philippe de Vlaminck]

Hereby a link to the data:

https://drive.google.com/open?id=0B7PlYvW7IcVnSTJRSGRQRGJsOFk

There is one day of data and the pivot table in there.

Grts,
Phil

 

[Andrew Poulsom]

First you need to repeat the labels in columns A:C. Then you could put this array formula in a spare column:

=IF(H12=MIN(IF(A$12:A$79=A12,IF(B$12:B$79=B12,IF(C$12:C$79=C12,H$12:H$79)))),H12,"")

confirmed with Ctrl+Shift+Enter. Finally average the column containing those formulas.

 

[Philippe de Vlaminck]

Thx Andrew ill give it a shot

Grts,
Phil