Performance Analysis: Weekday Function

**pwall1115** · 11-26-2012, 08:56 PM

Hey,

I'm trying to do a standard performance review where I need to organize data from a large worksheet into useful graphs.

I want to compare the avg sales from weekdays vs weekends. The problem is the data has many rows and I don't want to go line by line to first figure out which dates are weekdays and which are weekends, and then have to add the sum of all the sales and divide it by the total days.

I also want another graph that compares sales on Mondays, vs sales on Tuesdays, Wednesdays etc.........does anyone have any advice on how I would go about this?

THANK YOU SO MUCH!

example excel.xlsx

**Pete_UK** · 11-26-2012, 09:05 PM

There is a WEEKDAY function which returns numbers representing the days of the week. Used like this:

WEEKDAY(A1,2)

Mondays will be 1 up to Sunday being 7, so weekdays and weekends can be identified quite easily as being less than 6 or greater than 5 respectively.

Also, you can apply the TEXT function to a date column and return the day directly, like this:

=TEXT(D2,"ddd")

which will give Mon, Tue, Wed etc, and these can be used within a COUNTIF or SUMIF function to get counts or totals by different days.

Hope this helps.

Pete

**pwall1115** · 11-26-2012, 09:18 PM

Wow, Pete thanks for the prompt reply!

I just used your functions and now I have added its respective day of the week to each row. Is there a quick way I can summarize all sales for Mondays? Tuesdays? etc?

Is there a quick way I can summarize figures by Quarters (Jan-March, April-June, July-Sept, Oct-Dec)?

**pwall1115** · 11-26-2012, 09:21 PM

Or also group all weekdays together to find their avg sales and compare against the average sales on weekends?

Thanks again for all your help, you and this forum are quite impressive!

**Pete_UK** · 11-26-2012, 09:39 PM

I've just put together a simple table using SUMIF showing how you can summarise your data. I've used full-column references so that it will apply to your real data. You might like to count the number of days (using COUNTIF) and you can get %ages quite easily from the summary table.

Hope this helps.

Pete