Smart way to bin 8760 hourly data (series) based on several criteria?

[OaklandJim]

Have a series of 8760 data values. Data includes adjacent dates and times columns too. Want to bin based on date and time and whether weekday or weekend. . E.g. all values between two certain dates, between two hours per day and weekday or weekend.

Have a separate sheet with all inputs regarding dates and times.

Wuz about to embark on a VBA odyssey to write a sub that does the binning. I bet that there is a better way? I like sumproduct but that may be too ugly given all the inputs. I'm a low-power pivot tabler but maybe that? Maybe old fashioned Excel criteria "table" (I have totally forgotten how to do that)?

Details

For any one one bin it is for one each of the following criteria: five time periods, two "seasons" (specified by start and end date) and whether weekday or weekend so I think I need a formula of some sort to calculate all bins at once.

I have several named data ranges of series data (8760 one row each). The one with data to bin is named SeriesValues. Other 8760 series values -- whose data criterion should be self-obvious given names -- that can be used to help include: Series_DayOfYear (integers 1 - 365), Series_HourOfDay (integers 1- 24), Series_DayOfWeek (integers 1- 7), SeriesDates, SeriesTimes.

I have other named cells with the "screening" criteria/values needed: StartDate, EndDate, StartTime, EndTime.

So for values in range SeriesValues I want to sum and in them based on five criteria: : StartDate, EndDate, StartTime, EndTime and whether weekend or weekday.

Aside: based on what I see using Excel's weekday formula that weekend days of week are #7 -- Saturday and #1 -- Sunday.

 

[James006]

Hi,

Seems first step should be to test the Frequency function:

MS Excel: How to use the FREQUENCY Function (WS)

This Excel tutorial explains how to use the Excel FREQUENCY function with syntax and examples. The Microsoft Excel FREQUENCY function returns how often values occur within a set of data. It returns a vertical array of numbers.

www.techonthenet.com

Hope this will help

 

[dms37]

sounds like you're after a sumifs or countifs formula - use the weekday formula with the second argument as 2 to get Saturday and Sunday as 6 & 7 (so the helper column for Weekend is just =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

 

[OaklandJim]

Hi,

Seems first step should be to test the Frequency function:

MS Excel: How to use the FREQUENCY Function (WS)

This Excel tutorial explains how to use the Excel FREQUENCY function with syntax and examples. The Microsoft Excel FREQUENCY function returns how often values occur within a set of data. It returns a vertical array of numbers.

www.techonthenet.com

Hope this will help

Cool that function but I need the sum (i.e., binning), not frequency of occurrence. Thnx tho.