Automatic adjustment of cell references

a sample spredsheet would help, see yellow banner
But an INDIRECT() with a count() may help to change the column reference. May also need the Address/column() function as well

SO B6 stays the same and the column C, D,E,F,G, H, etc changes dependant on how many replies are present

But I think i need to see some examples of different tests and people and expected results

can you explain a little more detail, i dont understand your spreadsheet content, so i need some guidance since you said

The number of people taking the test changes each time.

How does excel know how many people took the test , what area of the sheet - All i can see is the same students A to F = 6

Currently looks like =t.test(B6:M6,B16:M16,2,1).

where is this formula - what cells 12 columns

Sorry trying to help, but just dont follow

will any of the replies actually result in zero, 0 as a valid answer in the cell?

as zero is valid, then how will excel know how many students have replied in columns B to M - as the default is zero and can also be a valid number

There may only be 10 replies in row 6 , But if a student replies 0 to all 10 - it will still show the 12 zeros in B6:M6

My thought was whether a user could input the number of students directly into another cell, giving excel the direct value.

that may work
And would for that sheet , only ever be the same number of students replying for all rows

Also is it only the range D46 to D50 thats impacted by the range change

Assuming the number of students is the same throughout

This is a bit clunky and can be further automated - but for showing the working i have laid it out longhand , if you like

The number of Students are entered into N3 - that is then used to decide on the columns to include

So Range will be B to whatever letter the number of students represent
As B is column 2 , we will need to add 1 to the column to get the final column - ie 4 Students = B,C,D,E = column 2 to column 5
I use
SUBSTITUTE(ADDRESS(1,$N$3+1,4),"1","")
this returns the letter

Now we need to build up the ranges we want to use
to fit into the function B5:M5 and B15:M16 and for the 6 rows - B5, 6,7,8,9,10
so i have built that in longhand in D54 to E59
In cells D52 i have put the first starting row - in this case 5 and in E52 = 15
BUT that could be automated - depending if you are likely to change the layout

Now to calculate the ranges
="B"&B54&":"&SUBSTITUTE(ADDRESS(1,$N$3+1,4),"1","")&B54
So we have B then we concatenate the value in B54 , which is 5 , then the : and then get the letter for the number of students , then add the row again from B54

now for t.test
=IF(T.TEST(INDIRECT(D54),INDIRECT(E54),2,1)<0.05,"<0.05",">0.05")

we want to substitute the range with the value in D54 and E54
to get the formula to use the contents of a cell in a formula - we use indirect

As i say a bit clunky and a bit longhand to spellout what i have done - can all be simplified, but wanted to make sure it actually achieved the result

I have also change the average in cell O5 to use the same range as derived from number of students

thoughts?

ok, you re welcome - BUT as i say it is clunky and there will be a better way to setup and possible avoid so many helper columns

But i will wait and see what issues you come up with or when you confirm its all working perfectly