# Calculate values based on time period

#### lomax

##### Board Regular
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>

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

Last edited:

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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 ...

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.

Last edited:
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.

Replies
9
Views
261
Replies
4
Views
393
Replies
11
Views
400
Replies
8
Views
474
Replies
1
Views
325

1,203,757
Messages
6,057,164
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back