Averaging Sales Totals By Day Of The Week In Microsoft Excel 2010

In this article, we will learn how to find average sales totals by day of the week in Microsoft Excel 2010.

To find the average sales total by particular day of the week, i.e. we want to determine which day of the week corresponds with each date in column A, and then calculate an average sales figure for each day of the week over the whole period. That is; we want an average sales total for all of the Mondays, one for all the Tuesdays, and so on.

We will use a combination of AVERAGE, IF & WEEKDAY functions to extract the output.

AVERAGE function can be used to find the average value or arithmetic mean of values in a selected range of cells.

Syntax: =AVERAGE(number1,number2,...)

Arguments

number1, number2,……number n are numeric values. They can be numbers or names, arrays, or references that contain numbers.

IF function checks whether the condition you specify is TRUE or FALSE. If the condition returns TRUE then it returns preset value, and if the condition returns FALSE then it returns another preset value.

Syntax = IF(logical_test,value_if_true,value_if_false)

logical_test: Logical test will test the condition or criteria. If the condition meets then it returns the preset value, and if the condition does not meet then it returns another preset value.

value_if_true: The value that you want to be returned if this argument returns TRUE.

value_if_false: The value that you want to be returned if this argument returns FALSE

WEEKDAY: It returns a number from 1 to 7 identifying the day of the week of a date

Syntax: =WEEKDAY(serial_number,return_type)

serial_number: This represents the date of the day that you want to find.

return_type: This is optional. A number that determines the type of return value.

Let us take an example:

We have some dates in column A & sales on these days in column B.
We have used TEXT function in column C to look for the day (Monday to Sunday). In cell C2, enter the function as =TEXT(A2,"dddd")& copy down in below range C3:C16.
We need a formula in column D to find the average sales total by any specific day of the week.

In cell D2, the formula would be

{=AVERAGE(IF(WEEKDAY(A2)=WEEKDAY($A$2:$A$16),$B$2:$B$16))}

This is an array formula, which requires formula to be enclosed with curly brackets by using CTRL + SHIFT + ENTER.

We get the average sales for Friday 350 as =AVERAGE(400,200,450)
Copy down the formula from cell D2 to range D3:D16. While copying, you need to select cell D2 first, and then you need to select range D3:D16 & then paste the formula, and you will get the desired result.

Bill McNicoll
May 29, 2014 at 4:08 pm

Why does the data in column B not appear in the formula? Also, I am running Excel 2007 and the WEEKDAY function has two required arguments.

Reply ↓