CODEWITHSUNDEEP
arraysexcel
Vince
Vince

Reputation: 45

Calculating Daily Averages with Excel

What would be the best way to calculate the daily average of a whole number over a given period.

For example:

Jan 1st | 456
Jan 2nd | 238
Etc all the way to the current date.

The end result is I would have the current daily average for the YTD, so like this :

Monday - average of 250
Tuesday - average of 183

Based on the YTD data. It's one column of dates and one column of numbers.

Any help would be appreciated.

Upvotes: 1

Views: 1664

Answers (3)

Forward Ed
Forward Ed

Reputation: 9874

Assuming your date was a string and combine thoughts from Tim and Scott, I came up with he following:

Place the following in an adjacent column and copy down to suit your data:

=WEEKDAY(DATE(2016,MONTH(LEFT(A1,3)&"-1"),MID(A1,5,1+(LEN(A1)=8))))

It will generate a list of integers 1-7 representing the day of the week.

In another cell, lets say E2 for example, place the day of the week you want to get the average of and beside it use the following formula:

=IFERROR(AVERAGEIF($C$1:$C$10,MATCH(E2,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0),$B$1:$B$10),"")

Proof of concept

Proof of Concept

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

If you want a formula then:

=AVERAGE(IF((WEEKDAY($A$2:$A$367)=D2),$B$2:$B$367))

This is an array formula and must be confirmed with Ctrl-Shift-Enter to exit edit mode instead of Enter. If done properly Excel will put {} around the formula.

I put 1-7 in D2-D8 to denote the weekday number. The Weekday formula returns a number so we needed that column for reference.

enter image description here

If the helper column is not wanted we can achieve the same using a counterROW(1:1) which will increase by one as the formula is copied down:

=AVERAGE(IF((WEEKDAY($A$2:$A$367)=ROW(1:1)),$B$2:$B$367))

This is still an array but is not dependent on the helper column D. Remember that the week starts on Sunday.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Add a column for the day of the week:

=CHOOSE(WEEKDAY(A1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

Then use a pivot table on the three columns of data to get the daily average for the year by day.

Upvotes: 1

Related Questions