I have some data where there is a day of the week, and a sales number for the day. I want to make a prediction for the remaining days of the month by taking the average of the last 7 weekdays if the day is a weekday or if the day is a weekend, the last 2 days of the same day (so if the day is Saturday, an average of the sales from the last 2 Saturdays). What would be the best way to implement this? Appreciate any help.
I tried using the AVERAGE function with QUERY, but just got an error.
Ex. =AVERAGE(A1:C31, QUERY("SELECT C WHERE A IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') LIMIT 7))
I tried the above formula just to see if I could get for the weekdays and then try to incorporate if it was a weekend as well if it worked.