-1

I'm working in behavioral analysis and trying to implement a formula the would track and automatically update the number of behaviors per time period. We track the 10 previous days, which gives us insight into our mastering and fading processes. I began with conditional formatting for anything less than 1= green, anything more than 1=red. This collects data from another excel sheet, but I just would like it to automatically update for me if possible. Thanks!

I've used google quite a bit and found one similar post referencing tracking data for the previous two weeks. I'm not very good with sheets, so I've tried to copy and paste, but it is not working. This is the closest that I have found Finding the average from the last 14 days in Google Sheets

Here is demo: https://docs.google.com/spreadsheets/d/1CxSVFhRQbFXY1Shu4UW7nO57w2SwrBpZwEy4sVzSH-M/edit?usp=sharing

Ideally, I will be able to keep track of how many are happening per time period. Ideally, it would update automatically and tell me how many per time period for the previous 10 days.

0
0

You are tracking behaviours per time period. The behaviours are: "Disruption" and "Task Refusal". You want to calculate the average of the last 14 days.

The layout of your spreadsheet is a barrier to calculation of the 14 day average. Dates are recorded on the horizontal axis (rather than vertical), and each date is merged across two columns - each column recording a behaviour.

I suggest a simplification of your data. Use only three columns: "Date", "Disruption", and "Task Refusal", and use the formula described in Finding the average from the last 14 days in Google Sheets to calculate the average.

In the following sample data, the formula are:

Disruption
=round(AVERAGE(QUERY({A2:C},"select Col2 where Col2 is not null order by toDate(Col1) desc limit 14", 0)),1)

Task Refusal
=round(AVERAGE(QUERY({A2:C}, "select Col3 where Col3 is not null order by toDate(Col1) desc limit 14", 0)),1)


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.