VBA lookup

[delexcel]

Hi,

I have the following code:

=IFERROR(MIN(
    IF(TEXT($A$2:$A$154810,"mmm")=$L40,
    IF($B$2:$B$154810>=N$38,
    IF($B$2:$B$154810<=N$39,
    IF(ISNUMBER($C$2:$C$154810),
    $C$2:$C$154810))))),"")

At present it is searching range $A$2:$A$154810, comparing the month of a cell in the range, to cell L40, which states a month.

If the month is the same, then it is searching range $B$2:$B$154810 to check whether the time of a cell in the range is between two different times (N38 & N39).

If all of those are true, then it returns a number in the range $C$2:$C$154810.

MY QUERY ...

At present, the first query is just looking up the month (mmm) of the date in the range $A$2:$A$154810.

Is it possible to ALSO look up the day (d) of the date in the range? And compare it to a 'day value' (1-31) in cell M40?

I tried modifing:

IF(TEXT($A$2:$A$154810,"mmm")=$L40,

to

IF(AND(TEXT($A$2:$A$154810,"mmm")=$L40,TEXT($A$2:$A$154810,"d")=$M40),

but unfortunately that did not work.

 

[delexcel]

What I am attempting to do ...

I have a data set of hourly temperature readings, spanning 6+ years.

I needed to ascertain the minimum temperature, for each month of the year, across each of the years (i.e. the minimum temperature in January in ANY of the 6+ year).

And then to refine it even further, it needed to be the minimum temperature for the month BETWEEN specified time periods (6am-9am, 3-5pm, etc.).

The above was successfully completed using the following code:

=IFERROR(MIN(
    IF(TEXT($A$2:$A$154810,"mmm")=$L40,
    IF($B$2:$B$154810>=N$38,
    IF($B$2:$B$154810<=N$39,
    IF(ISNUMBER($C$2:$C$154810),
    $C$2:$C$154810))))),"")

However I now need to find the MEAN MINIMUM.

So to do so, I need to find the minimum for each day, between the specified time periods.

Once I have the daily minimums, I can then average each of those across the month, to produce an average minimum for that month (for that particular year).

This will be replicated over every month in the 6+ year data set.

And then the final step will be to average each of the months across the 6+ years.

For instance,

Start by calculating the minimum temperature readings, between specified time periods, for each day of the year.

Take all of the minimums above, and then average to give a mean minimum for the month - and do this for each year.

Jan 2000 average min = 1 deg
Jan 2001 average min = 2 deg
Jan 2002 average min = 3 deg
Jan 2003 average min = 4 deg
Jan 2004 average min = 5 deg
Jan 2005 average min = 6 deg
=> average of 3.5 degrees in the month of Jan


So that is why I wanted to amend the above code so that it could be used to automatically calculate the DAILY minimum - as at the moment it is (successfully) calculating the monthly minimum.

And as my last post said, the most recent discovery I had was that the code works if you limit the range to a day (refer my post Jun 30th, 2011 11:24 PM)

"The above code works because the range A50:A58 only spans one day.

As soon as the range is increased to more than one day the result returned is zero again."

 

[delexcel]

* Bump *

I know (and apologise) for the description (my last post) being so long however I felt everything needed to be explained clearly in order for a solution to be discovered.

Can anyone offer assistance towards a solution?