CODEWITHSUNDEEP
vbaexcel
GGA
GGA

Reputation: 385

Conditional mean calculation in excel

I have a dataset organized as following :

My dataset

The column A is the name

The column B is the date

The column C is the value registered for that person in that day

How can i calculate for the whole dataset a mean of the value of that person in the 30 past days without manually ordering for name and making the mean checking the date?

Upvotes: 0

Views: 674

Answers (3)

user4039065
user4039065

Reputation:

Try the AVERAGEIFS function with the EDATE function giving you a one month window.

=AVERAGEIFS(C:C, A:A, "Jack", B:B, ">"&EDATE(TODAY(), -1), B:B, "<="&TODAY())

    averageifs_dated

Upvotes: 3

Bjoern Stiel
Bjoern Stiel

Reputation: 4181

You can use a nested array formula (Ctrl+Shift+Enter instead of Enter):

=AVERAGE(IF($A$2:$A$15=A2,IF($B$2:$B$15>=TODAY()-30,$C$2:$C$15,""),""))

Upvotes: 1

durbnpoisn
durbnpoisn

Reputation: 4669

Add columns for these two fomulas:

=COUNTIF(A1:A4,"Jack")
=SUMIF(A1:A4,"Jack",C10:C13)

That will give you the count, and it will give you the sum. With those two you can calculate the mean. That's the basic idea, anyway. Of course, you can add another count for the date ranges. It's all the same sort of thing.

Upvotes: 0

Related Questions