Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Plutonium Lounger
    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.

  4. #4
    2 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •