CODEWITHSUNDEEP
google-sheets
Brandon
Brandon

Reputation: 117

Formula to calculate historical data based off a specific condition?

I am trying to observe historical trends on customer acquisitions (new and returning) and am looking to use a formula to automate it for me.

Essentially, I am looking to determine the average amount of new customers we acquire on a specific day, specific week, and specific month. For example: what are the average customers we have acquired every Monday for the past 6 months, or what is the average number of customers we acquire the first week of every month?

Column F, I, and L would be the averages I calculate. Column A and B are the days of the week/date those numbers fall on.

Upvotes: 0

Views: 62

Answers (1)

CMB
CMB

Reputation: 5163

Solution:

You can use the date operators in your QUERY statement to filter by month, week, or even day of week.

Examples:

every Monday for past 6 months

=query(A1:B, "select avg(B) where datediff(todate(now()),todate(A)) < 180 and dayofweek(A) = 2", 1)

first week of every month

=query(A1:B, "select month(A),avg(B) where day(A) <= 7 group by month(A) offset 1", 1)

You would need to tweak the sample queries to cover your data range and which columns do you need to average and compare.

References:

QUERY()

Query Language Reference | Scalar Functions

Upvotes: 1

Related Questions