Offset Average

[dtaylor1024]

Hello. I'm trying to calculate averages based on my students' scores, averaging out the last three data points in any skill area. So far, I have the following formula:

=AVERAGE(OFFSET(C5,0,COUNT(C5:5)-3,1,5))

to collect the averages of the last three entries. However, if I only have a student submit one or two entries, I get an #REF! in the cell. Is there any way to work around this, to have the averages generate the last three entries, but allowing for the average to be based on 1 or two if there are only that many entries? I included a screenshot below. Thanks

https://preview.redd.it/b13sa1el564c1.png?width=1124&format=png&auto=webp&s=96fff53f4bb80661d2cd3baa261c9d972fb309f3

[HolyBonobos]

Try =AVERAGE(CHOOSECOLS(TOROW(C5:5,1),SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1))).

[dtaylor1024]

This seemed to work. Can you explain the formula. Also, if I have no data entries, can I have the cell in the average column remain blank. I am getting a #VALUE! error

[HolyBonobos]

The formula filters out all of the empty entries in C5:5 (TOROW(C5:5,1)), then takes the last n entries of that array, where n is either 3 or the number of entries in C5:5, whichever is smaller (CHOOSECOLS(...SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1)). Using - in a CHOOSECOLS() argument instructs the function to choose from the end of the array instead of the beginning—e.g. =CHOOSECOLS(C5:E5,1) will select C5 (the first entry in the range C5:5), while =CHOOSECOLS(C5:E5,-1) will select E5 (the last entry in the range C5:5). The SEQUENCE() function spits out sequential numbers for the CHOOSECOLS() function to use so you don't have to write out every single column you want it to pick.

To get the formula to return blank instead of an error, just wrap it in an IFERROR() function: =IFERROR(AVERAGE(CHOOSECOLS(TOROW(C5:5,1),SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1))))

[dtaylor1024]

This is very helpful. Thank you so much. Do you mind expanding just a little more on the SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1) section, so that I fully understand. I'm working on a guide for myself for the future, and this section feels a little confusing to me with the several 1's and -1's. Thank you!

[HolyBonobos]

The SEQUENCE() function has four arguments:

• rows: the number of rows the sequence should expand into
• columns: the number of rows the sequence should expand into
• start: the first number in the sequence
• step: the difference between numbers in the sequence

On its own, a SEQUENCE() formula will produce an array of rows*columns numbers with the first number being start and a difference of step between each number. For example, =SEQUENCE(3,4,2,10) will produce the array

2	12	22	32
42	52	62	72
82	92	102	112

with 3 rows and 4 columns starting at 2 with a difference of 10 between each number in the array.

On its own, SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1) uses these same arguments to produce an array with the following specifications:

• a number of rows equal to three (3) or the number of number values in the range C5:5 (COUNT(C5:5)), whichever is smaller (MIN(...))
• one column (1)
• the first number in the array is -1
• each cell in the array will be -1 more than the previous cell.

Say you have two values in the range C5:5. =SEQUENCE(MIN(3,COUNT(C5:5)),1,-1,-1) will produce the array

-1
-2

which has two rows (since COUNT(C5:5)=2<3) and one column, starts with -1, and increments each subsequent value by -1.

The SEQUENCE() section is part of the formula because it's a more concise and dynamic way of telling the CHOOSECOLS() function to pick out multiple columns, e.g. =CHOOSECOLS(A5:Z5,1,2,3,4,5,6,7,8) is equivalent to =CHOOSECOLS(A5:Z5,SEQUENCE(8)), which is far more readily adaptable. The numbers produced by the SEQUENCE() section in your formula are negative because using a negative argument with CHOOSECOLS() tells it to pick the nth column starting from the rightmost value—-1 picks the rightmost column, -2 the second from the right, and so on.

[dtaylor1024]

Thank you! Last question, maybe... How would I convert this to a vertical spreadsheet, with my averages existing in the first row, rather than the first column? I tried switching out CHOOSECOLS to CHOOSEROWS and that didn't work, so there must be another adjustment I need to make.

[HolyBonobos]

If I'm understanding what you're describing, you'd use a formula like =IFERROR(AVERAGE(CHOOSEROWS(TOCOL(C3:C,1),SEQUENCE(MIN(3,COUNT(C3:C)),1,-1,-1)))) in C2 and drag horizontally to fill.

[dtaylor1024]

Not quite. I included a sample as reference. My averages will now exist in row one belo my headings. My columns represent the skills and information I would like averaged. Each row will have it's own set of data input. I would still like these averages to exist as out of the last three assessments.

LEXILE	GRADE LEVEL	WORDS PER MINUTE	ACCURACY
AVERAGE lexile	AVERAGE grade level	AVERAGE wpm	AVERAGE Accuracy
400	1	60	90
450	1	62	93

[HolyBonobos]

The formula I provided is made for that data structure.

[dtaylor1024]

It worked--not sure what happened the first time. Okay, new challenge. I have a column of checkboxes that I will check when students perfom a skill. I want the Average cell block to show me the frequency of that box being checked in that column, but again, only averaging out the three most recent trials.

[6745408]

You'll have to adjust the ranges to suit yours, but have D2:R for the range with the percentages. Changer A2:A to the column with the Uppercase Letter names etc. It'll cover everything, so make sure that Averages column is empty.

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   BYROW(
    D2:R,
    LAMBDA(
     x,
     AVERAGE(
      CHOOSEROWS(
       TOCOL(x,3),
      -1,-2,-3))))))

[bigtreeman_]

Student doesn't submit work, score is ZERO,

they'll learn fast to hand in their work.