How to separate my dataset in weekdays and week-ends?

Hey guys!

I have in this file energy consumption for every 15min of every day of the year 2015. I want to get a few things out of this:

1) I need to be able to separate to weekdays from the week-ends as consumption differs quite a lot
--> Maybe there's also a way for me to add a list of national holidays and see those days go to the week-end category automatically?
2) I want season average for these 2 categories (so I will have 8 series: 4 seasons divided in 2 set of days. For each series I will have data for the average day every 15 min)
--> I can manage this part easily
3) I want my 8 series to be divided in 3 categories: high, medium, low
--> I want 3 equal categories. I should be able to do this with percentiles

Thanks a lot people for you help people

In CZ2 I'd put:

Formula:

Please Login or Register  to view this content.

in DA1:DA13 a list of your national holidays

I left only some first rows of data to keep file small
note formulas in DA1:DA13 - may be you can use some of the dates there (like Christmas, Easter Monday, Ascension Day, etc.), while other dates, like Polish Constitution Day (May 3rd) etc. are probably useless for you

Hi -

Use the following formula to convert dates to days of the week:

=CHOOSE(WEEKDAY(CONCATENATE(B2,"/",A2,"/",C2)),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") Note, since I am not on European calendar, my date concatenation is reversed from yours (switch B2 and A2).

I inserted a column and added the days in column D.

Then use SUMPRODUCT to add up the days you want. I have written this formula for a small part of the data set so I could easily check the results.:

=SUMPRODUCT((($E$2:$G$26)*($D$2:$D$26="Saturday"))+(($E$2:$G$26)*($D$2:$D$26="Sunday"))) This one sums up all the usage on Sat/Sun.
=SUMPRODUCT((($E$2:$G$26)*($D$2:$D$26<>"Saturday"))*(($D$2:$D$26<>"Sunday"))) This one sums up all the weekday usage.

It's simply a matter of addding more conditions to SUMPRODUCT to get the rest of your items (Seasonality, Holidays, etc.). Use + on your conditions to create an OR response (e.g., Saturday OR Sunday). Use multiplication (*) to create and AND conditions (e.g., Not equal to (<>) Saturday AND Sunday). You can add a list of Holiday dates, Season ranges, etc.

The following link is an AWESOME explanation of SUMPRODUCT. You can do virtually anything with this function.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Good luck.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.