-1

I work with teachers who have to fill out information in a Google Sheet every day. There is a column for date and a column for incident counts. They log in and add a row with a new date and incident count every day.

Here is a link to a version of the spreadsheet anyone can access:https://docs.google.com/spreadsheets/d/1tiFe99q1mTIWlsIh2v9toPWE1QTfBZ6F2_rpKlWNi8g/edit?usp=sharing

They want to see a single cell at the top of the spreadsheet (there are a few rows for meta-data calculations above where the headers and data entry begin -- I know, not best practice), populated with the most recent 10-day rolling average number of incidents.

I understand how to make a column with a 10-day rolling average: just =AVERAGE(), with cell on the same row and the 9 cells above in the incident count column.

However, how do I make a single cell at the top of the spreadsheet that either:

  1. is alway filled in with the last value in the 10-day rolling average column or
  2. calculates the 10-day rolling average based on the incident column (don't need a 10-day rolling average column at all)

And if I do need a 10-day rolling average column, how do I write a formula that makes the cell blank if not all of the 10 cells are numeric (it would include headers or blanks), so it doesn't calculate it for any row that doesn't have enough days for a 10-day average?

Thanks for considering my question! I use DAX for PowerBI, and I would use LASTDATE. Wondering if Google Sheets has that same concept, but in a formula.

ACK
  • 39
  • 1
  • 1
  • 8
  • 3
    Decide Excel or Googlesheets. – Solar Mike Apr 29 '23 at 04:07
  • Hint: find the row number of the last non-blank cell then subtract 10 for the start of the average range. – Solar Mike Apr 29 '23 at 05:56
  • 1
    In Excel a combination of AVERAGE TAKE TOCOL gets that, but that's different from Google sheets – P.b Apr 29 '23 at 07:12
  • Thanks @P.b.! If I didn't get a google sheets answer, I was going to try and google terms I learned from an excel answers to help me learn and think creatively. I'll still research what you posted so I can learn! Thank you! – ACK Apr 30 '23 at 06:35
  • 1
    `=AVERAGE(TAKE(TOCOL(D:D,1),-10))` takes the average of the last 10 non-empty rows – P.b Apr 30 '23 at 07:30

2 Answers2

3

Within Sheets you may try:

  • Added 2 scenarios since I couldnt get a clear gist of how the input data is goin' to be
  • Added an if condition to not go ahead with the average calculation if a minimum of 10 values aint found within D25:D

#1 if incident column (D25:D) strictly accommodates values only (no blank cells OR text values in the midst)

=let(Σ,counta(D25:D),if(Σ>9,round(average(offset(D25,Σ-10,,10)),2),"-"))

enter image description here

#2 if incident column (D25:D) has mixed set of data as shown in screenshot

=let(Σ,D25:D,
     Δ,choosecols(sortn(filter({Σ,row(Σ)},isnumber(Σ),Σ<>""),10,,2,0),1),
       if(counta(Δ)>9,round(average(Δ),2),"-"))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • rockinfreakshow, you are incredible. Thank you. I realize I was so bleary-eyed after fruitlessly trying to figure it out, I hadn't re-named the yellow cell "rolling average of 10-day incident count" instead of percent, so that was confusing. But even with my mistake on that, you NAILED IT!! THANK YOU!! I ended up using your "mixed set of data" example". Lots for me to learn and unpack here. Thank you! – ACK Apr 29 '23 at 20:38
0

Try this:

=QUERY($A$24:$D$136,"select avg(D) where B >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and B <= date '"&TEXT(TODAY()+10,"yyyy-mm-dd")&"' label avg(D) ''",0)

Tedinoz
  • 5,911
  • 3
  • 25
  • 35