AUTOFILL ACROSS/HORIZONTALLY ISSUE

[Data123]

hi I have a sheet named "data" with data that looks like this in figure "a" below.

then in another sheet named "weekly scores" see fig b. i use this formula to get david's scores for cells b2 and f2 =('data'!B2-'data'!F2)/'data'!F2*100

i would like to autofill across, in the "weekly scores" sheet (fig b), but when i do the autofill gives me the wrong column letters that are calculated. how can i fix this please?


fig a

A B C D E F

Date	2024-12-20	2024-12-19	2024-12-18	2024-12-17	2024-12-16
David	35	33.76	33.16	34.76	34.12

fig b
A B

Week 1	Week 2
2.58

 

[Kaper]

Try for instance:

=LET(weekN,COLUMNS($A1:A1),startD,INDEX(2:2,1,5*weekN+1),endD,INDEX(2:2,1,5*weekN-3),(endD-startD)/startD*100)

and copy it to right as needed.

Another option:
use empty row for this formula, because it spills to the right:

=LET(weekN,SEQUENCE(1,3),startD,INDEX(2:2,1,5*weekN+1),endD,INDEX(2:2,1,5*weekN-3),IFERROR((endD-startD)/startD*100,""))

and change 3 (sequence second argument) to number of weeks you want to have filled

 

[Kaper]

PS. Yet another step - automatic calculation of weeks data available based on dates in row 1

=LET(weekN,SEQUENCE(1,INT(COUNT(data!$1:$1)/5)),startD,INDEX(2:2,1,5*weekN+1),endD,INDEX(2:2,1,5*weekN-3),(endD-startD)/startD*100)

Again write it only in first cell it will spill to the right as needed.

PS. in such questions sample data for second week and expected result for it are always a good idea.
Here at first it looked rather clear, but may be it is not.

The formula above (and formulas in previous post) assume you have only working days in row 1. If you have there also weekends you may try:

=LET(weekN,SEQUENCE(1,ROUNDUP(COUNT(data!$1:$1)/7,0)),startD,INDEX(2:2,1,7*weekN-1),endD,INDEX(2:2,1,7*weekN-5),(endD-startD)/startD*100)

and similar modifications (7*weekN and changed number deducted after) in formulas from my previous post