+ Reply to Thread
Results 1 to 10 of 10

Creating a dynamic calculation within groups

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    Creating a dynamic calculation within groups

    Hi,

    I am trying to automate a calculation based on information from different columns and not quite sure if it's possible. This is what I am looking to do. Within the Reformat Data Sheet, first I am grouping by vial so for all the Areas within that group I am looking for the highest value, when found I take the RT value and use it as the denominator for the other RT values and put that new value in the RRT column. The files can be as large as 8000 rows with several groups so automating it will save lots of time. See example below:

    Vial A,1 group - max value corresponds with an RT of 4.219

    SampleName Vial Vial Id RT Area RRT
    0-INITIAL A,1 1468 0.614 6769 0.146
    0-INITIAL A,1 1468 0.668 1007 0.158
    0-INITIAL A,1 1468 0.709 858 0.168
    0-INITIAL A,1 1468 1.071 653 0.253851624
    0-INITIAL A,1 1468 6.741 719 1.597771984
    0-INITIAL A,1 1468 6.984 454 1.655368571
    0-INITIAL A,1 1468 1.875 419 0.444418109
    0-INITIAL A,1 1468 2.533 316 0.600379237
    0-INITIAL A,1 1468 2.969 815 0.703721261
    0-INITIAL A,1 1468 3.748 2631 0.888362171
    0-INITIAL A,1 1468 4.219 1844241 1
    0-INITIAL A,1 1468 6.433 1102 1.524768903
    0-INITIAL A,1 1468 6.67 142 1.580943352

    Is it possible, can someone please help me?

    Thanks,
    Michelle
    Attached Files Attached Files

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Creating a dynamic calculation within groups

    Not sure if this is what you are wanting. Test on a copy of your Worksheet Reformat Data

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Creating a dynamic calculation within groups

    I assumed that the Vial ID was really what you wanted, since the Vial A,1 was repeated further down the column with a different max area.
    Attached Files Attached Files
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Creating a dynamic calculation within groups

    Yes, that works great! And I was able to understand and change the formula to round to 3 decimals. Thanks so much!!!

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Creating a dynamic calculation within groups

    Could just use instead of changing the formula

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Creating a dynamic calculation within groups

    Bernie,

    Wow 2 ways to solve my issue! This is great to know but I think that the macro may be the way to go as the number or rows in the file may go down to 8000-10000 and the number of points within each group can also vary.

    Thanks! I will keep that formula in my pocket too.

  7. #7
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Creating a dynamic calculation within groups

    Oh OK I made the formula:
    .FormulaArray = "=round(D2/INDEX(D$2:D$" & lr & ",MATCH(MAX(IF(C$2:C$" & lr & "=C2,E$2:E$" & lr & ")),E$2:E$" & lr & ",0)),3)"

    It seemed to work well. Is one way better than the other?

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Creating a dynamic calculation within groups

    I would just change one time then to have the code calculate every line

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-01-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2010
    Posts
    64

    Re: Creating a dynamic calculation within groups

    Thank you both for your help!

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Creating a dynamic calculation within groups

    Just be aware that the two formulas could return different RT values if there is ever a tie for the maximum value in area - not sure if RT and area are completely related - the INDEX version will return the first value, and mine will return the max value.

+ 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. Autofill Dynamic Range (empty row in between groups)
    By nekef in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2016, 11:54 PM
  2. Creating dynamic userform rely on dynamic checkbox selection
    By END93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2016, 06:39 PM
  3. [SOLVED] Dynamic generation icon groups in ribbon 2007~
    By bagullo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-19-2015, 10:34 AM
  4. Creating Number Sequence in Groups of 10
    By DBWalsh12 in forum Excel General
    Replies: 8
    Last Post: 02-21-2013, 05:55 PM
  5. Creating Dynamic Folders/WorkBook from a Sheet with Dynamic Path.
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-28-2011, 05:42 AM
  6. Creating Groups
    By KINKASI in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2009, 06:21 PM
  7. [SOLVED] creating groups based upon correlations
    By Boom1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-23-2006, 04:45 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