Sort SCADA data and finding MAX & MIN values

[S_T996]

Hi,
I have a small challenge with the following parameter where I would be very great full for some help.

2 columns of data, the first A contains sets of calendar dates:
A1 27-4-2013
A2 27-4-2013
A3 27-4-2013
A4 28-4-2013
A5 28-4-2013
Op to 5000 lines but normally around 140 of them for each date in April 2013
Colum B contains wind speed data from a met tower
B1 15,0
B2 5,3
B3 7,9
B4 3,2
B5 32,4
What I need to find are the Highest, Lowest and Average wind speed for a specific date.

I have a NOW -1 function in field C1 I would like to use as the parameter to search for in column A so I get the last full calendar days full data set (10min values), so writing it up it sounds something like this:
Look in column A line 1:5000 and if you find any date the same as in C1 then move the content of B to a new separate column D line 1:140 and when all 140 lines are extracted for the specific date then find the Highest, Lowest and Average wind speed between those extracted data.

I have been told that the below formulas would work but I can’t them to do so and all I get is an error code.

For the MAX value use this array formula (must be entered with Ctrl+Shift+Enter, not just Enter):
=MAX(IF(A1:A5000=DATE(YEAR(C1),MONTH(C1),DAY(C1)),B1:B5000))

For the MIN value use this array formula (must be entered with Ctrl+Shift+Enter, not just Enter):
=MIN(IF(A1:A5000=DATE(YEAR(C1),MONTH(C1),DAY(C1)),B1:B5000))

For the AVERAGE value use this formula entered with just Enter:
=AVERAGEIF(A1:A5000,DATE(YEAR(C1),MONTH(C1),DAY(C1)),B1:B5000)

Thanks in advance for any help and suggestions
S_T996

[XOR LX]

Hi,

On the face of it I can see no reason why the formulas you were given should not give the desired result. Is it possible for you to post a sample of your workbook?

[mrice]

Seems to work for me - please see the attachment.

[S_T996]

Seems to work for me - please see the attachment.

Thanks mrice,

But what makes it work on your sheet and not on mine with all the original data? I have attached my original data this sheet if you or somebody else could please take a look.

Thanks
S_T996

[S_T996]

The problem seems to be of the format kind as I was starting to fear.

The format I get from the SCADA system are like this 29-04-2013 10:00 but if I use a NOW -1 or TODAY -1 function there are only 1 10 minute value that actually matches.

Is there a function to make it look only at dates and forget about the time stamp?

Thanks
S_T996