Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Need you help again. ASAP if possible.

    Hi guys!

    I would like to thank everyone who assisted me with my 2 posts before.

    I have another template attached and i hope anyone can help me.

    Here's the deal, in Cells J3: J23, i want a formula wherein it will list the "group name" from cells B2: B11 based on the corresponding percentage in Cells F2: F11.

    I hope this is clear enough.

    Please see attached template to make it clear
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Wouldn't it be possible for all 10 groups to fall into 1 of the three sections? If so, you'd need 10 spaces for them all to be shown.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi tadzkie

    kweaver makes a good point. I assume you have considered this aleady.

    In the attached file, if you change the %age values in column [F], the corresponding values will be shown in the appropriate section in column [K] (try it for yourself!).

    I have applied custom formats in column [F] to match the Summary colour groupings.

    The results displayed in column [K] are in numeric order, i.e. the largest values are shown first within each of the three Summary colour-coded sections.

    I have applied custom numeric formats in column [K] to hide zero values.

    Duplicate % values are assumed to be allowed in column [F].

    If there are more values than will fit in the Summary section, the 'top' values are shown until that section is 'filled'.

    I have left out the formulas for column [J] for other experts to have a go at.

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    tadzkie (2016-03-27)

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I took another approach (rather complex) and added a reference column, then tested for your % values and removed the blank results. Phew.

    In my example, you can change the group number in K2 as long as it's a single digit and the last digit in the K2 cell.

    My formulas in the J column are array formulas FYI (CTRL+Shift+Enter), but there's no need to change them unless you have to move things elsewhere.

    This might be better handled in a macro, but that's not my skill set.
    Attached Files Attached Files
    Last edited by kweaver; 2016-03-27 at 16:27.

  6. The Following 2 Users Say Thank You to kweaver For This Useful Post:

    tadzkie (2016-03-27),zeddy (2016-03-27)

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi kweaver

    ..nice method!

    Your results in each Summary section are in 'row sequence' (i.e. in the green section, first value encountered in column [F] which is 30% and above appears first, second value encountered in column [F] which is 30% and above appears second etc etc)

    zeddy
    Last edited by zeddy; 2016-03-28 at 05:55.

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..next version (works OK if there are no duplicate values in column [F]


    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    tadzkie (2016-03-27)

Posting Permissions

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