+ Reply to Thread
Results 1 to 5 of 5

Auto-select cell ranges based on x column value to execute formula

  1. #1
    Registered User
    Join Date
    09-04-2014
    Location
    Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    2

    Auto-select cell ranges based on x column value to execute formula

    Hi,

    I have a large data set for which I must calculate 'mean weighted scores' for many individuals. Although I know how to do this one at a time, I was wondering if there was a function (or VBA code which I'm less experienced with) which would allow me to do this for every individual at once.

    \1

    In the above example image I have manually calculated the Weighted Score for each person (ID 1, 2 and 3) but to do so I had to select the relevant cell ranges (e.g. C2:C6 and B2:B6 for ID 1) myself. Is there a way to automatically select these cell ranges based on the value in the ID column?

    Thank you!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Auto-select cell ranges based on x column value to execute formula

    Try this in cell F2 and copy down.

    =SUMPRODUCT(--(A$2:A$13=E2),C$2:C$13,B$2:B$13)/SUMIF(A$2:A$13,E2,B$2:B$13)

    There's probably an easier way, but this is mine.

    BSB.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto-select cell ranges based on x column value to execute formula

    seeing the layout in your .png:

    I would solve this kind of problems with an pivot table.

    maybe it is neccesary to add an helpcolumn to determine the weight of each row.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    09-04-2014
    Location
    Australia
    MS-Off Ver
    Professional Plus 2010
    Posts
    2

    Re: Auto-select cell ranges based on x column value to execute formula

    Quote Originally Posted by BadlySpelledBuoy View Post
    Try this in cell F2 and copy down.

    =SUMPRODUCT(--(A$2:A$13=E2),C$2:C$13,B$2:B$13)/SUMIF(A$2:A$13,E2,B$2:B$13)

    There's probably an easier way, but this is mine.

    BSB.
    Thank you so much, that works perfectly!

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Auto-select cell ranges based on x column value to execute formula

    Happy to help.

    Don't forget to mark the thread as SOLVED if you're satisfied you have your solution.

    BSB.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Select cell based on the last entry in other column
    By Kehjz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-03-2013, 02:44 PM
  2. Replies: 1
    Last Post: 03-08-2012, 10:07 PM
  3. select column based on cell input
    By jcfrancisco in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-04-2011, 09:52 AM
  4. select entire column based on cell value
    By jcfrancisco in forum Excel General
    Replies: 5
    Last Post: 09-26-2011, 09:47 AM
  5. Programming a Macro to select row ranges based on a formula
    By workaholic in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2009, 09:32 PM

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