Results 1 to 4 of 4

20060713, 15:32 #1
 Join Date
 Dec 2001
 Posts
 174
 Thanks
 0
 Thanked 0 Times in 0 Posts
formula variable auto calculate (2000+)
Hi all
Pls see attachment which calculates center of gravity a system as shown in final row.
I want to be able to add extra rows of data and obtain the result in final row automatically.
1. Is it possible to do this just by using formulas in final row?
2. How can it be done using vba?
3. Pls see question in attachment required syntax for entering varible in formula?
Many thanx
Smbs

20060713, 15:42 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: formula variable auto calculate (2000+)
In B15:
=SUMPRODUCT($A$2:$A$14,B2:B14)/SUM($A$2:$A$14)
Copy B15 to c1515
If you insert rows between 2 and 14 it will automatically recalculate...
Steve

20060713, 15:44 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: formula variable auto calculate (2000+)
You can use dynamic named ranges, on condition that you move the calculations to other columns, so that columns A through D contain only data.
 Select Insert  Name  Define...
 Enter Mass as name.
 Enter the following formula in the Refers to box:
=OFFSET($A$2,0,0,COUNT($A:$A),1)
 Click Add.
 Also create names x, y and z with similar formulas using column B, C and D instead of A.
 To calculate the x coordinate of the center of gravity , use this formula:
=SUMPRODUCT(Mass,x)/SUM(Mass)
 Similar for y and z.
The result will be updated automatically as you edit, add or remove data. See attached workbook.

20060713, 15:48 #4
 Join Date
 Dec 2001
 Posts
 174
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula variable auto calculate (2000+)
Many thanx to both of u will give it a try
Smbs