Dynamic number of vars in formula

Yoni17

New Member
Joined
Jul 15, 2018
Messages
9
[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]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,619
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top