Formula to calculate the number of weeks that it takes for a score to interstect the predicted score

[Rnkhch]

Hello,

I came up with a way of gauging student progress, and I’m trying to write a formula for that purpose, so I can test the method in larger scale to see if it’s any good at all. Here is what I do: I take each student’s week 1 score and subtract it from the week 5 score (e.g. 88-76), and so there is an improvement of 12 points over 5 weeks, or 2.4 points per week, for this example (table 1).

Now, the question I want to ask is, at any given week, how many weeks does it take for the student’s score to intersect the predicted score, which is the score of that week plus 2.4 x #weeks of passed? I calculated manually as shown in table 2, so for example, for week 1, it takes 2 weeks (table 3) for the score to intersect week3 score (i.e. 81 is greater than 76+2.4+2.4). And at week2, it takes 1 week for the score (81) to intersect the predicted score (80.4).

I need a formula that would calculate this without the need to make the extra table 2 of predictions.

Thanks for any input!!!


Table1:

	Week1	Week2​	<strike></strike> Week3​	<strike></strike> Week4​	<strike></strike> Week5​	<strike></strike> Week6​	<strike></strike> Week7​	<strike></strike> Week8​	<strike></strike> Week9​	<strike></strike> Week10​ <strike></strike>
Student1	76	78	81	79	88	86	90	83	91	95
etc.

<tbody>
</tbody>

<strike></strike>Table2 (predicted scores):

	Week1	Week2​	<strike></strike> Week3​	<strike></strike> Week4​	<strike></strike> Week5​	<strike></strike> Week6​	<strike></strike> Week7​	<strike></strike> Week8​	<strike></strike> Week9​	<strike></strike> Week10​ <strike></strike>
W1pred.		78.4	80.8	83.2	85.6	88	90.4	92.8	95.2	97.6
W2pred.​ <strike></strike>	<strike></strike>		80.4	82.8	85.2	87.6	90	92.4	94.8	97.2
W3pred.​ <strike></strike>	<strike></strike>			83.4	85.8	88.2	90.6	93	95.4	97.8
W4pred.​ <strike></strike>	<strike></strike>				81.4	83.8	86.2	88.6	91	93.4
W5pred.​ <strike></strike>	<strike></strike>					90.4	92.8	95.2	97.6	100
W6pred.​ <strike></strike>	<strike></strike>						88.4	90.8	93.2	95.6
W7pred.​ <strike></strike>	<strike></strike>							92.4	94.8	97.2
W8pred.​ <strike></strike>	<strike></strike>								85.4	87.8
W9pred.​ <strike></strike>	<strike></strike>									93.4

<tbody>
</tbody>

Table3 (# of weeks taken for the actual score to intersect the predicted score):

	Week1	Week2​	<strike></strike> Week3​	<strike></strike> Week4​	<strike></strike> Week5​	<strike></strike> Week6​	<strike></strike> Week7​	<strike></strike> Week8​	<strike></strike> Week9​	<strike></strike> Week10​ <strike></strike>
Student1	2	1	2	1	never	1	never	1	1	n/a

<tbody>
</tbody>

 

[Tetra201]

Assuming that:
- the number of weeks is fixed to 10, and
- you have week numbers (1 to 10, without the "Week") in range B1:K1, and
- you have scores in range B2:K2,

try putting the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) in a cell and drag-copying it to the right as needed:

=IFERROR(MATCH(TRUE,OFFSET($B2,,B$1,,10-B$1)>=OFFSET($B2,,B$1-1)+(OFFSET($B2,,4)-$B2)/5*TRANSPOSE(ROW(INDIRECT((B$1+1)&":10"))-B$1),0),IF(AND(B$1>0,B$1<10),"never","n/a"))

 

[Rnkhch]

Wow, amazing! You are an absolute genius!!!

If you get a chance, could you please provide a brief explanantion of the formula as well?

Thanks a lot!

 

[Tetra201]

You are welcome.

Here is a brief explanation:

The OFFSET($B2,,B$1,,10-B$1) part creates an array of actual student scores. Since we have 1 in cell B1, the array has 9 (i.e., 10-1) elements: 78,81,79,88,86,90,83,91,95.

The (OFFSET($B2,,4)-$B2)/5 part calculates the the score improvement per week over the first 5 weeks.

The OFFSET($B2,,B$1-1)+(OFFSET($B2,,4)-$B2)/5*TRANSPOSE(ROW(INDIRECT((B$1+1)&":10"))-B$1) part creates an array of predicted scores. For week 1 (1 in cell B1), it's 78.4,80.8,83,2,85.6,88.0,90.4,92,8,95.2,97.6.

The two arrays are then compared (actuals>=predictions) to return the following array: FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE.

Then, the MATCH(TRUE,... function looks for the first occurrence of TRUE and finds in in position 2 of 9. This 2 is your result for Week 1.

If the MATCH(TRUE,... function cannot find TRUE, it returns an error (#N/A), which is handled by the IFERROR(... function that returns "never" or "n/a", depending on the week number.

Hope this helps.

 

[Rnkhch]

Awesome! Really well-designed!!! I'm gonna digest it, and I'll post back if I have more questions. Thanks much!!!

 

[Rnkhch]

There is one part that I couldn't exactly figure out. I think that the

TRANSPOSE(ROW(INDIRECT((B$1+1)&":10"))-B$1​

part is a counter mechanism to calculate how many times to add 2.4 (i.e. score improvement), is that right? But I couldn't figure out how it is doing the counting Thank you!

 

[Tetra201]

You are right. Here is how it works:

We have 1 in cell B1, so
B$1+1 evaluates to 2
(B$1+1)&":10" evaluates to "2:10"
INDIRECT((B$1+1)&":10") evaluates to range 2:10 (i.e, rows 2 to 10)
ROW(INDIRECT((B$1+1)&":10")) evaluates to a vertical array of numbers {2;3;4;5;6;7;8;9;10}
ROW(INDIRECT((B$1+1)&":10"))-B$1 evaluates to a vertical array of numbers {1;2;3;4;5;6;7;8;9}. See *Note below.
TRANSPOSE(ROW(INDIRECT((B$1+1)&":10"))-B$1) evaluates to a horizontal array of {1,2,3,4,5,6,7,8,9}
(OFFSET($B2,,4)-$B2)/5*TRANSPOSE(ROW(INDIRECT((B$1+1)&":10"))-B$1) evaluates to a horizontal array of {2.4,4.8,7.2,9.6,12.0,14.4,16.8,19.2,21.6}
OFFSET($B2,,B$1-1)+(OFFSET($B2,,4)-$B2)/5*TRANSPOSE(ROW(INDIRECT((B$1+1)&":10"))-B$1) evaluates to a horizontal array of {78.4,80.8,83,2,85.6,88.0,90.4,92,8,95.2,97.6}

*Note: this could have been done in a simpler way: ROW(INDIRECT("1:"&(10-B$1)))

 

[Rnkhch]

I see. That's amazing! I have learned so much from your formula! Highly appreciated!

In the meantime, I was applying the formula to many of my score sheets, and it works so nicely!

Lastly, there is one issue that I encountered, which I hope you can help me with. Not sure why, but some of the scores have been entered backwards, meaning week10 is in the left, and week1 is rightmost. If not a big trouble, could you please make a variant of your formula for this kind of sheets? If it's too complicated, don't worry about it; I will try to flip the orientation of scores somehow. I tried to make one, but my brain gave up Thanks much!

 

[Tetra201]

Assumptions for the "reverse-order" layout:
- the number of weeks is fixed to 10;
- you have week numbers (10 to 1, without the "Week") in range B1:K1;
- you have the corresponding actual scores in range B2:K2.

Try putting the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) in a cell and drag-copying it to the right as needed:

=IFERROR(LOOKUP(2,1/(TRANSPOSE(A2:$B2)>=B2+($G2-$K2)/5*(11-B$1-ROW(INDIRECT("1:"&(10-B$1))))),11-B$1-ROW(INDIRECT("1:"&(10-B$1)))),IF(AND(B$1>0,B$1<10),"never","n/a"))

 

[Rnkhch]

Fantastic! Thanks a lot!!!