Trying to calculate and capture data from a previous rolling 90 days.

Kris Le

As you can see below, I have a data set that is pulling information from another sheet and calculating to provide the below information. Right now, the same formulas are copied into the "Rolling 90 Day" results that are being used in the "Entire Data Set" results field.

Using Line 2 as an example, I'm using the following formula:

=AVG(COLLECT({Assay Trending Range 1}, {Assay Trending Range 2}, 1)), Where:

"Assay Trending Range 1" = All assay results in the entire data set; AND

"Assay Trending Range 2" = A check box field that is check when I want the data to report.

In the sheet which contains the raw data, I also have a "Date of Results" field. What I can't figure out, is that I want to use the same formulas above, but I want them to filter out the results from the previous 90 days OR TODAY(-90). I want to be able to compare recent results to the total data so that I can identify trends in real time. I've tried several iterations and nothing seems to work, please help!

Mark Cronk

Hi @Kris Le ,

=AVG(COLLECT({Assay Trending Range 1}, {Assay Trending Range 2}, 1, {Insert Date of Results Range}, AND(@cell<=today(), @cell>= today(-90)))