My iMac have system version 10.15.6 and Numbers version 10.1. I have daily data in Table 1, first column Date and second column Units. In Table 2, my first column is the beginning of a week date calculated from the Date Column in Table 1. I want to calculate the weekly unit averages for the second column of Table 2. In Excel I would embed average equation that would cover the most recent 7 daily values. Can one embed math operations that will change the cell references?
Thanks, Riley
—–
Riley Willcox
You can do this with AVERAGEIFS. That would use the Units column as the amounts and then test the Date column twice. Once to see if the date was the same or greater than the week start date, and once to see if it was also less than the week start date + 7. So something like this:
AVERAGEIFS(Units,Daily Data::A,">="&A2,Daily Data::A,"<"&(A2+7)) In this case, A2 of Table 2 contains the week start date, like 1/15/2020. The first test looks at column A of Table 1, which has the dates. If it is >= the value of A2, then it it on that day or after. The second test also looks at the A column, and if it is less than the start date + 7, then it is true. So if both are true that row is in the week's range. All of those are added to the average.