Dynamic number of vars in formula

[Yoni17]

[FONT=&quot]How can i change the number of vars in this formula dynamically based on a number in another cell.[/FONT]
[FONT=&quot]For example:[/FONT]
[FONT=&quot]=(ABS(P3-Q22)+ABS(P4-Q22)+ABS(P5-Q22)+ABS(P6-Q22)+ABS(P7-Q22)+ABS(P8-Q22)+ABS(P9-Q22)+ABS(P10-Q22)+ABS(P11-Q22)+ABS(P12-Q22)+ABS(P13-Q22)+ABS(P14-Q22)+ABS(P15-Q22)+ABS(P16-Q22)+ABS(P17-Q22)+ABS(P18-Q22)+ABS(P19-Q22)+ABS(P20-Q22)+ABS(P21-Q22)+ABS(P22-Q22))/20[/FONT]
[FONT=&quot]Number is : 20[/FONT]
[FONT=&quot]If the number is 14, then the formula should be:[/FONT]
[FONT=&quot]=(ABS(P9-Q22)+ABS(P10-Q22)+ABS(P11-Q22)+ABS(P12-Q22)+ABS(P13-Q22)+ABS(P14-Q22)+ABS(P15-Q22)+ABS(P16-Q22)+ABS(P17-Q22)+ABS(P18-Q22)+ABS(P19-Q22)+ABS(P20-Q22)+ABS(P21-Q22)+ABS(P22-Q22))/14[/FONT]

 

[Peter_SSs]

One more thing, as the number of vars is dynamic (which is E1 in your example), P22 can not be static but should be a formula on P3 (maybe P3+number in E1 -1)?

Difficult to understand what you actually do want. In your original post, P22 was static when "the number" was changed from 20 to 14. What did change was the starting point: P3 changed to P9. My suggested formula took into account that changed starting cell.

 

[Yoni17]

Difficult to understand what you actually do want. In your original post, P22 was static when "the number" was changed from 20 to 14. What did change was the starting point: P3 changed to P9. My suggested formula took into account that changed starting cell.

I will try to explain better.
First, i tried the formula with sumproduct, it is working when the number is 20. it is also working when applying it to 200K rows (was not too heavy after all)
I changed the formula a little to : =SUMPRODUCT(ABS(INDEX(T3:T22,1):T22-U22))/$W$1 and it is still OK.
What is missing is simple, now the formula works when the number is 20, but if i change the number to 14 it does not work.
Why? because the range of T3:T22 should be only 14 vars and not 20. that means T3:T16
So T16 should be dynamic according to the number

Hope i managed to explain better.
Thank you for the effort

 

[Peter_SSs]

So from that I am guessing that the 20 is in cell W1. Is that correct?

Assuming the above is correct, if you were just writing the formula manually for when W1 is 14, what would the formula be?

 

[Yoni17]

- Yes, the number is in cell W1
- Exactly as in my original post (i updated the cells):

Number=20
=(ABS(T3-U22)+ABS(T4-U22)+ABS(T5-U22)+ABS(T6-U22)+ABS(T7-U22)+ABS(T8-U22)+ABS(T9-U22)+ABS(T10-U22)+ABS(T11-U22)+ABS(T12-U22)+ABS(T13-U22)+ABS(T14-U22)+ABS(T15-U22)+ABS(T16-U22)+ABS(T17-U22)+ABS(T18-U22)+ABS(T19-U22)+ABS(T20-U22)+ABS(T21-U22)+ABS(T22-U22))/W1


If the number is 14, then the formula should be:
=(ABS(T9-U22)+ABS(T10-U22)+ABS(T11-U22)+ABS(T12-U22)+ABS(T13-U22)+ABS(T14-U22)+ABS(T15-U22)+ABS(T16-U22)+ABS(T17-U22)+ABS(T18-U22)+ABS(T19-U22)+ABS(T20-U22)+ABS(T21-U22)+ABS(T22-U22))/W1

So from that I am guessing that the 20 is in cell W1. Is that correct?

Assuming the above is correct, if you were just writing the formula manually for when W1 is 14, what would the formula be?

 

[Peter_SSs]

- Yes, the number is in cell W1
- Exactly as in my original post (i updated the cells):

If the number is 14, then the formula should be:
=(ABS(T9-U22)+ABS(T10-U22)+ABS(T11-U22)+ABS(T12-U22)+ABS(T13-U22)+ABS(T14-U22)+ABS(T15-U22)+ABS(T16-U22)+ABS(T17-U22)+ABS(T18-U22)+ABS(T19-U22)+ABS(T20-U22)+ABS(T21-U22)+ABS(T22-U22))/W1

Then I don't understand you saying my original formula doesn't work for 14

In the sheet below
- in cell X1 I have the exact formula from post 2, apart from the changed column/cell references.
- in cell Y1 I have pasted your formula from post 14.
The results are identical.

If you still think my formula is not giving the results you expect, can you post some sample data for T3:U22 where my formula gives an incorrect result when W1 contains 14?
If so, what result does my formula give? What is the correct result?
(My signature block below has a link for methods for posting screen shots similar to this)

Book1
	T	U	V	W	X	Y
1				14	2.857142857	2.857142857
2
3	7
4	6
5	6
6	1
7	5
8	3
9	2
10	5
11	3
12	8
13	8
14	1
15	7
16	9
17	5
18	3
19	3
20	5
21	2
22	9	7
Yoni17

Cell Formulas
Range	Formula
X1	=SUMPRODUCT(ABS(INDEX(T3:T22,ROWS(T3:T22)-W1+1):T22-U22))/W1
Y1	=(ABS(T9-U22)+ABS(T10-U22)+ABS(T11-U22)+ABS(T12-U22)+ABS(T13-U22)+ABS(T14-U22)+ABS(T15-U22)+ABS(T16-U22)+ABS(T17-U22)+ABS(T18-U22)+ABS(T19-U22)+ABS(T20-U22)+ABS(T21-U22)+ABS(T22-U22))/W1

 

[Yoni17]

Tried it with different numbers and it works as long as the number is less than 20, right?
What is missing is: the number can be any number. it can be 1,5,14,20, 500, 10000... does it work with a number greater than 20?
The formula will not work if the number is greater than the range in your formula (=20), right?
Can you change the range in the formula to be dynamic according to the row number (T3 can be static) and end of the range which is now T22 but should be dynamic (T3+the number-1) ?
When changing the number (1, 5, 14, 20, 10000) i do not wish to change the formula itself.
Thank you Peter

Then I don't understand you saying my original formula doesn't work for 14

In the sheet below
- in cell X1 I have the exact formula from post 2, apart from the changed column/cell references.
- in cell Y1 I have pasted your formula from post 14.
The results are identical.

If you still think my formula is not giving the results you expect, can you post some sample data for T3:U22 where my formula gives an incorrect result when W1 contains 14?
If so, what result does my formula give? What is the correct result?
(My signature block below has a link for methods for posting screen shots similar to this)

	T	U	V	W	X	Y

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2.857142857[/TD]
[TD="align: right"]2.857142857[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Yoni17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]X1[/TH]
[TD="align: left"]=SUMPRODUCT(ABS(INDEX(T3:T22,ROWS(T3:T22)-W1+1):T22-U22))/W1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Y1[/TH]
[TD="align: left"]=(ABS(T9-U22)+ABS(T10-U22)+ABS(T11-U22)+ABS(T12-U22)+ABS(T13-U22)+ABS(T14-U22)+ABS(T15-U22)+ABS(T16-U22)+ABS(T17-U22)+ABS(T18-U22)+ABS(T19-U22)+ABS(T20-U22)+ABS(T21-U22)+ABS(T22-U22))/W1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

As I indicated before, it has been confusing because the requirement seems to keep changing. Lets look at your stated requirements for when the number is 14:
Post 1 said the formula used rows 9:22 (exactly the same end row as for when the number was 20)
Post 8 you said row 22 was not static and we should use row 3 + number -1 (rows 3 to 16)
Post 12 again confirmed rows 3:16
Post 14 you reverted to "Exactly as in my original post" & gave the formula using rows 9:22 again
Post 16 it is back to 3 + number -1 (rows 3 to 16)
Understand why somebody not familiar with your worksheet or what you are actually trying to do could be a little unsure?

OK, so let's look at row 3 being the fixed starting row and 3 + number -1 being the dynamic ending row (eg rows 3 to 16), which I assume also applies to column U.

=SUMPRODUCT(ABS(T$3:INDEX(T$3:T$100000,W1)-INDEX(U$3:U$100000,W1)))/W1

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, just quote small, relevant parts only.