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]

Welcome to the MrExcel board!

Suppose "the number" is in cell E1, then try

=SUMPRODUCT(ABS(INDEX(P3:P22,ROWS(P3:P22)-E1+1):P22-Q22))/E1

 

[Yoni17]

I should run this formula on 100K rows so sumproduct will be a little heavy to calculate...

Welcome to the MrExcel board!

Suppose "the number" is in cell E1, then try

=SUMPRODUCT(ABS(INDEX(P3:P22,ROWS(P3:P22)-E1+1):P22-Q22))/E1

 

[Peter_SSs]

I should run this formula on 100K rows so sumproduct will be a little heavy to calculate...

I answered the question you asked, not the one I had no idea existed.

Do you mean there are 100,000 rows in the range being looked at (eg P3:P22 in your sample) or you are running lots of these formulas all looking at about 20 rows?

Have you tried the formula?

 

[Yoni17]

You are absolutely right and thank you for your answer.
Yes, the formula works.
The second option- i need to apply/ copy this formula (20 or 14 vars) on the next 100K rows

 

[Peter_SSs]

You are absolutely right and thank you for your answer.
Yes, the formula works.
The second option- i need to apply/ copy this formula (20 or 14 vars) on the next 100K rows

I can't help with specifics on that because I don't know where the first formula is or where "the number" actually is.
Is it the same "the number" for all rows or does it change for each row?
Unless I can recreate what you have, I cannot test.

 

[Yoni17]

The number is the same for all rows. what will change is just the cells numbers inside the formula that will be adjusted by the each row
I don't need more than 1 formula that will not have sumproduct as it is heavy for the excel to calculate for many rows. i will adjust the formula for my needs.
Can you adjust the formula that you wrote not to have sumproduct in it or is it the only option?

 

[Yoni17]

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)?

 

[Yoni17]

The formula is working when "the number" is 20 but not when it is 14

 

[mikerickson]

The number is the same for all rows. what will change is just the cells numbers inside the formula that will be adjusted by the each row
I don't need more than 1 formula that will not have sumproduct as it is heavy for the excel to calculate for many rows. i will adjust the formula for my needs.
Can you adjust the formula that you wrote not to have sumproduct in it or is it the only option?

Your task is heavy. Any formula will (like Sumproduct) reflect that.