Calculate values based on time period

[lomax]

Hi All,

I am trying to calculate values in one column based on a time period within a separate column.

The 'time' column and corresponding 'Lq' values are not always in the same columns on each sheet. I started my marco with the find function but am lost after that.

In the following data set I would like to calculate the average Lq value between the time period 14:40 - 15:00

Record	Date	Time	Lq
File 1	08/06/2015	14:35	68.6
File 2	08/06/2015	14:40	67.8
File 3	08/06/2015	14:45	70.2
File 4	08/06/2015	14:50	68.3
File 5	08/06/2015	14:55	68.5
File 6	08/06/2015	15:00	69.9
File 7	08/06/2015	15:05	68
		Average 14:40 - 15:00	68.7

<tbody>
</tbody>

 Range("A1").Select
    Cells.Find(What:="Time", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

 

[James006]

Hi,

Is there a specific reason for a macro ...???

In fact, it looks like it is a pretty standard situation ... which can be handled by formulas ...

 

[onlyadrafter]

Hello,

if in F1 you enter 14:00 and G1 you enter 15:00, you could use this formula

=SUMPRODUCT(--(C2:C8 > =F1),(--(C2:C8 < G1))*D2:D8)/(COUNTIF(C2:C8,">="&F1)-COUNTIF(C2:C8,">="&G1))

without the spaces.

there must be a simpler formula, but times are a bit of a struggle for me.

 

[lomax]

Thanks for looking guys. I am getting help on this elsewhere now.

Yes there is reason for a macro as the worksheets contain reams of data that need values to be extracted for certain time periods.

I will post code when I have it working or admin can close thread if they wish.