+ Reply to Thread
Results 1 to 5 of 5

Aggregations of a matrix

  1. #1
    Registered User
    Join Date
    06-17-2022
    Location
    Boston
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20172) 64-bit
    Posts
    3

    Lightbulb Aggregations of a matrix

    I created a skills matrix with my team and was hoping to create a top 10 list of our highest scored skills vs lowest scored skills, and I am unsure of the best way to do so in Excel. I started by trying a filter (which I will include below).



    A snippet of the matrix (smaller example)
    image_2022-06-17_111437274.png

    My FILTER I was using was to at least filter down to certain ranges. The G7 and I7 refer to thresholds that I set. The FILTER doesn't really do what I wanted, but it was the next best thing I could think of.
    Please Login or Register  to view this content.
    My ideal result would be something like this:

    Top skills (X<5)
    Accessibility Remediation
    Apache
    Accessibility Auditing
    Accessibility (WCAG/508)

    Bottom Skills (1<=X<=5)
    .Net
    Angular
    ASP.NET MVC

    No Skill (x<=0)
    AWS
    Ansible

    Most of my google searches show me how to create a matrix or crosstab using pivots, but do not work with one that I already have.

    Any ideas on methods to accomplish this?
    Attached Files Attached Files
    Last edited by emeria; 06-17-2022 at 05:59 PM.

  2. #2
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    Microsoft Office 365
    Posts
    1,083

    Re: Aggregations of a matrix

    See yellow banner at the top with how to post a sample workbook. This makes all our lives easier so we don't have to recreate your data via a picture.
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Registered User
    Join Date
    06-17-2022
    Location
    Boston
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20172) 64-bit
    Posts
    3

    Re: Aggregations of a matrix

    Thanks, added an example!
    Last edited by AliGW; 06-18-2022 at 03:26 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,106

    Re: Aggregations of a matrix

    In the gfile, G7 & I7 are empty.

    What do you expect to see? Where do you expect to see it?

    Please mock up your expected results on the file.
    Glenn



  5. #5
    Registered User
    Join Date
    06-17-2022
    Location
    Boston
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20172) 64-bit
    Posts
    3

    Re: Aggregations of a matrix

    Quote Originally Posted by Glenn Kennedy View Post
    In the gfile, G7 & I7 are empty.

    What do you expect to see? Where do you expect to see it?

    Please mock up your expected results on the file.
    Sorry about the confusion, the G7 and I7 referred to are on the sheet "Skill Views". The formula using G7 and I7 is in B8. (The other two filters are in B16 and B24 of the Skill Views sheet)

    My desired results are on the sheet "ExampleDesiredResults". I want to be able to see my top X or bottom X skills, as well as be able to find skills that we have no proficiency in (shown in B2 to B15). Ideally, I could see a unique list of what team members have skills for that list of skills (shown in B21 to B25). I included a screenshot on that sheet, that just breaks the problem down to a smaller set of skills.

    Let me know if you still want me to improve the example workbook or if you have more questions.

+ 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. Data Aggregations' with Index and Matich
    By CHICWS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2021, 04:50 PM
  2. [SOLVED] Convert qty of matrix items to matrix groups
    By OAKLEY in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-12-2019, 06:31 PM
  3. [SOLVED] 9 Cells Matrix / GE Matrix chart hellp
    By vani2004 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-20-2018, 02:34 PM
  4. How to link different data aggregations in Power Pivot?
    By coolhandphil in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-24-2018, 12:28 PM
  5. Replies: 2
    Last Post: 11-05-2014, 03:41 AM
  6. Replies: 2
    Last Post: 02-11-2014, 05:05 AM
  7. Complex Aggregations - Looking for Help
    By Xcelguy in forum Access Tables & Databases
    Replies: 5
    Last Post: 11-16-2013, 07:47 AM

Tags for this Thread

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