I have a worksheet with 2 columns, 1

[sedgely]

I have a worksheet with 2 columns, what i want to do is to check if cell 1 in column A falls between, say 01:00 and 02:00 then to look to see which other cells in column A also fall between those times and average the values in the corresponding cells (row) in column B.
so in the example below rows 1 thru 5 are within the time range therefore i would want to average B1:B5

A B
1 01:10 21
2 01:15 22
3 01:09 23
4 01:53 24
5 01:06 25
6 02:10 13
7 02:15 24
8 03:21 32
9 03:56 6
10 04:02 12

can anyone help?

Craig

 

[xlbo]

what's the criteria ?? ie what are the time periods to "fall in" ???
Is the data sorted ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[sedgely]

the time criteria is hourly i.e 00:00-00:59, 01:00-01:59 etc and the data in column A is sorted.

Craig

 

[xlbo]

That being the case, this should work:

SUM((HOUR(A1:A10)=HOUR(A1))*(B1:B10))/SUM((HOUR(A1:A10)=HOUR(A1))*(1))

array entered (CTRL+SHIFT+ENTER)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DaleWatson123321]

Craig,

Based on your example data, I expect you want to be able to create a Summary of the averages for EACH hour of the day.

Based on your data, I've developed a model for you that has a separate formula for EACH hour. These formulas are placed on a separate "Summary" sheet.

The formulas use the DAVERAGE function - one of Excel's "database" functions. Others are DSUM, DCOUNTA, etc.

The database functions are QUITE "dynamic" and POWERFUL. Unfortunately, Microsoft has done a less-than-desirable job of documenting this special component of Excel.

The file I've developed for you should serve as an example to get you "jump started" in understanding and utilizing the POWER of these functions.

There's another important aspect of these database functions. The same "criteria" used by the functions can also be used to either:
a) Filter the data in place, or
b) Extract a copy of the data to a separate sheet.

This extraction capability enables one to easily generate "detail" reports to back up the Summary "totals".

This "filtering" can be found on Excel's menu: Data - Filter - Advanced Filter.

So if you'd like me to email you the file, simply email me at my work address below, and I'll send the file.

Please note: I have other example files that demonstrate the capability of the database functions, and VBA code that utilizes the Advanced Filter extraction capability. If you would also like these files, just ask.

This offer (of emailing the files) is also extended to anyone else who would like to "expand your horizons" on this relatively un-tapped but POWERFUL component of Excel.

A final point to "wet the appetite"... Because the Advanced Filter component was developed "internally", it's written in the C language, and is therefore MUCH faster in extracting or filtering data than the conventional looping routines using straight VBA.

Hope this helps

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[sedgely]

Dale
Many thanks for your files, i have now had time to implement your solution and it works brilliantly, have a star. i haven't got round to investigating the other files you sent me but i will, and i'm sure they'll be of use.
Once again many thanks

Craig

 

[DaleWatson123321]

Craig,

You're most welcome, and "thanks" for the STAR.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca