Calculate values based on time period

"values are not always in the same column"
Please send a short sample of your file

Here's a start:

Please Login or Register  to view this content.

A sample is really needed to be sure data is time and not text: treatment is not the same

Thanks for looking at this for me.

I tried the code but got Run-time error 13
Type Mismatch

Originally Posted by PCI

A sample is really needed to be sure data is time and not text: treatment is not the same

I always format the time column into 'hh:mm' for charting purposes.

Please see attached WB with 3 example data sets.

For now I would like to concentrate on the LAeq column and been able to calculate values within a certain time period. Once I get this working I'm hoping I can modify the code for other columns and time periods. The values are logarithmic however and I use the following function to calculate average value. e.g. =dbavg(A1:A10)

Please Login or Register  to view this content.

First the sample was very welcome because the display of the time is not the same and it's clear it is time vales
I a bit confused with the last "LAeq " information to treat as there is no date inside
Now try next code which is doing the average:
in sheet "Data Set 1"
in column with header = "Time"
for time between 14:40 - 15:00
Result is written in cell AA1
There is some idea to use a UDF with some arguments like : Sheet's name, Time start, Time End

Please Login or Register  to view this content.

Thanks PCI,

I note that your code returns a time value in your WB? (14:51:51)

When I run the macro in my WB it returns a value of 0.61934

I was looking to calculate the average of the corresponding LAeq values (Column I on 'Data Set 1' sheet) between 14:40 and 15:00. In this case it should return a value of 69.225 if you use the excel average (arithmetic mean) function. I want to use my dBAvg function instead which should return a value of 69.3

Time LAeq
14:34:21 68.6
14:39:21 67.8
14:44:21 70.2
14:49:21 68.3
14:54:21 68.5
14:59:21 69.9
15:04:21 68
15:09:21 68.2
15:14:21 68.4

OK now it's clear what to do

Please Login or Register  to view this content.

Thanks again PCI,

If I understand your code correctly, I think it is adding up the values within the time period and diving by total number of values to obtain the average.

Please Login or Register  to view this content.

Instead of doing this I am looking to select the values within the time period and use the 'dBavg' function. The dBavg function is in another WB along with many other functions I use and will need to call up. Note the values are logarithmic so you cannot do arithmetic average.

Please Login or Register  to view this content.

See here after a possibility
The result is changed to an array which can be an argument for your UDF

Please Login or Register  to view this content.

Hi Lomax,

Try these:

Please Login or Register  to view this content.

PCI / xladept,

Thank you both very much for your help. I think I should be able to adopt the code now for my use. I will need to work out how to call up the Add-In Functions in VBA or maybe it might be easier to include the function code within the macro.

I have seen a topic on it here but have not had time to work through it. http://www.fontstuff.com/vba/vbatut08.htm

Thanks again for your help.

Hi Lomax,

You're welcome and thanks for the rep!

*If you make the function an Add-In, you just call it the same way - by referring to it

The problem is that the endtime is less than the start time when you get into the next day - I'm puzzling over it now

I also found a couple of places to correct or improve the code - here's the night version with the code corrections but with the date problem still unsolved:

Please Login or Register  to view this content.

I think I got it

Please Login or Register  to view this content.

Hey xladept,

Yes that works for me, many thanks! Is see what you did (*24) but not sure how it works. Then again I'm not an excel guy!

Can I ask - If I had say 72hrs of data will it extract all values for given time period? Will I need to add that same line of code to the 07:00 - 23:00 time period?

Thanks again man

Hi Lomax,

You're welcome and thanks for the rep!

I don't know about the 72 hours, probably back to the drawing board

I got the same value for set 2 but the others are different

Please Login or Register  to view this content.

Thanks xladept,

Data Sets 1 & 3 seems to give correct answer. The returned value for Data Set 2 is incorrect. The values for time period 07:00 - 23:00 are in Cells(F5:F87,F281:F292). The code has skipped Cells (F5:F87) and only calculated for 23:00 - 23:55 (F281:F292).

It seems like the code searches for the StartTime and only includes values after that cell moving down the table. Is there anyway to get it to look back up the table?

Is it possible to include all values between the start and end time no matter what sequence they are in? I guess I'm talking about a range without knowing to much about it!

Hi Lomax,

I'm confused now - isn't the tally supposed to start at 23:00 on the 24Th??

Hey xladept,

No, the date shouldn't matter. The important part is that it calculates for all the data within the Time Period irrespective of date. So for Data Set 2 it should calculate for Cells(F5:F87,F281:F292) which contains data in the time period 23:00 - 07:00. I hope this make sense.

If you do =dbavg(F6:F88,F282:F293) in a cell this is the value the Night_LAeq_Avg macro should return i.e. 62.3

The marco currently returns 64.2

'dbavg' being the Function:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This gives 61.4 from 97 data points and set 3 goes up .2 ?????????

a random post....

another random post......

oh dear....another random post

You're welcome - again

Hi Lomax,

This will highlight the cells that go into the calculation - all, I can think of is that we should not include 7:00??

Please Login or Register  to view this content.

I know what the problem is - I may be able to fix it tomorrow

Hey xladept,

I think you got it. I have tested it on all data sets for time period 11:00 PM - 07:00 AM and its seems to be working correctly. Highlighting cells was a great idea also.

The only issue now appears to be with the time period 07:00 PM - 11:00 PM. For some reason it calculates all data. I have looked over the code and cannot work out why this might be. Please see attached sheet.

I'm not sure what could be causing this?


TimeCalc-R1.xlsm

I think there was a problem in passing the range to your function - so this does the calculation in the routine and bypasses the function

Please Login or Register  to view this content.

AND - Here's the day version:

Please Login or Register  to view this content.

removed code

Hi Lomax,

Look at my previous post - I added a day version (the logic uses AND re OR)

Hi Lomax,

I think I've got your Master program:

Please Login or Register  to view this content.

And here's how to invoke it

Please Login or Register  to view this content.

Hi xladept,

Thanks for that.

Edit: I see my mistake.

I just got the right values on set 2???

Hi Lomax,

Set 2 manual day should be:

Please Login or Register  to view this content.

Here's an improvement?

Please Login or Register  to view this content.

Hey xladept,

Many thanks for your help. It all looks good and it has shortened the amount of code needed. However, I think I am going to use the previous code as I like the fact that it fills the cells for each time period with different colors so it can be manually checked quickly.

I'm sure this thread is going to help many people in the future as there was very little information on 'calculating values between time periods' when I initially started to look for a solution.

Kudos to you xladept

PS. It wont allow me give you anymore reps! If I could I would.

Hi Lomax,

Thanks for all the beers and thanks for wanting to rep me again!

This will color the day yellow, the night light blue, and all light green:

Please Login or Register  to view this content.

Hey Xladept

Im back again! Please see attached

Hi Lomax,

This is better the last routine had too many convolutions at the end

Please Login or Register  to view this content.

You're welcome!

Hey xladept,

Back again!

I'm getting an error when I try to use the mode function within the code. The other functions seem to work ok.

The error returns 'unable to get the mode property of the worksheet function class'

I read elsewhere that this error occurs often when any argument passed to the worksheet function is not of the correct type or simply doesn't make sense.

I have removed parts of the code used for the average function and tried 'Dim U As Double' but doesn't seem to make a difference. Any ideas?

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

Where did it error - I tried it at 8.0 Hz and 250 Hz?

Could it error with no mode in the data?

Try this code - it will leave the mode empty if none exists:

Please Login or Register  to view this content.

If I remove the first line of the Lmax_Call Sub then it works for #11:00:00 PM#, #7:00:00 AM#

However it does not return same value as manual calc!?

Sorry didnt see your 2nd post. The resume next works as in it skips 07:00 - 23:00 so like you said there doesn't appear to be any data available.

Please Login or Register  to view this content.

This is what I got for modesExcept for the LAeq)


Data Range

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
294		Time Period		LAeq	LAFmax	LAF,90	8.0 Hz	16 Hz	31.5 Hz	63 Hz	125 Hz	250 Hz	500 Hz	1.0 kHz	2.0 kHz	4.0 kHz	8.0 kHz	16.0 kHz
295	Day 16hr	7:00 - 23:00		66.9				61.5	62.1	69.7		59.3	57.2	62.2	59.0	55.6	48.7	36.7
296	Night 8hr	23:00 - 7:00		61.3	74.2	35.5	45.6	51.0	57.3	55.9	57.2	50.3	51.6	53.2	54.0	43.4	30.2
297	All	7:00 - 7:00		66.3	74.0	62.7	46.1	63.7	66.5	69.6	64.6	60.3	59.7	63.4	58.9	49.8	39.0	39.1

Hi Lomax,

The U Range is a non-contiguous Union of Cells - I guess that the entire data set cannot be interpreted by the WorksheetFunction