+ Reply to Thread
Results 1 to 6 of 6

Weighing Point Clusters

  1. #1
    Registered User
    Join Date
    11-09-2006
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    200

    Weighing Point Clusters

    Hello !!!)
    I wanted to ask your help it creating a unified formula for weighing certain point clusters. I have a list of points where each has a weigh attributed to it. I have a cluster dimension parameter which defines how many points will be in one cluster. I am able to find the number of points belonging to one cluster but I need to extend this further to perform calculation NOT on the points belonging to the cluster but –indirectly – on the WEIGHTS which belong to all the points in the cluster. At first I need to AVERAGE the weights belonging to the points in the cluster. I need a formula approach to this question as I am going to tweak it extensively in the future for all kinds of indirect array calculations. Please see the attached workbook which shows in detail how the calculations should be made using one of the clusters – lying around the 21 point as an example.
    Thank you very much for your time!)
    Dima
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-09-2006
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    200

    Re: Weighing Point Clusters

    maybe someone can let me know if this is possible to do in principle?))
    Thanks!)

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Weighing Point Clusters

    matbe if you could define how or what a cluster is?
    but to start
    =IF(D3<>0,SUMPRODUCT(--($D$3:$D$32<D3+$F$1),--($D$3:$D$32>D3-$F$1)),0)
    gives the same result as your aray and just seems easier to understand and perhaps add to
    Last edited by martindwilson; 12-25-2010 at 03:08 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    11-09-2006
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    200

    Re: Weighing Point Clusters

    Thank you very much for this post - yes your formula replicates the results of my formula - the cluster is a group of points which are close to any single point - the cluster size parameter defines the distance above or below a point where the point can fall to below to the cluster:

    for example point - 20 - cluster size 3 - this means the cluster will contain all point which are larger than 17 and smaller than 23


    I need to perform the calculations on the wights corresponding to the members of the cluster



    thanks!!!!

  5. #5
    Registered User
    Join Date
    11-09-2006
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    200

    Re: Weighing Point Clusters

    hello ! I am able to calculate the average weight for the points which are equal to 20
    =AVERAGE(IF($D$2:$D$32=20,$A$2:$A$32))
    but when I add more conditions to the IF formula inside it shows zero - please tell me what is not working correct?)
    Thanks!)

  6. #6
    Registered User
    Join Date
    11-09-2006
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    200

    Re: Weighing Point Clusters

    Ok i found the solution!) =average(if($d$2:$d$200>d7-$f$1,if($d$2:$d$200<d7+$f$1,$a$2:$a$200)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1