+ Reply to Thread
Results 1 to 9 of 9

Unique values from array then average adjacent column values

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    Manchester
    MS-Off Ver
    Excel 365
    Posts
    10

    Red face Unique values from array then average adjacent column values

    Hi all,

    Wondering if someone could help.

    I'm trialling a new method of data collection from a beer tasting panel where we have several tasters who report different flavours as codes (called terms) and their intensity (called weight) and then give the product an overall score. We have up to 8 people at a time.

    Currently we just add all the scores together and input them on a sheet manually, but I wanted to try create a sheet where we store all the results and report back as well.

    What I'm trying to do is, collect all the unique "terms" in an array and deliver the average "weight" as a summary for each individual product tested. I've has a look through the forum and can't quite find what I'm looking for. I've attempted myself via google using the UNIQUE function, but I'm struggling when it's spread across multiple columns. If there's a simpler way of doing it that saves time using a calculator and averaging dozens of values then entering then back in a different sheet, then I'm all over that.

    I've attached my (very) rough attempt.

    Thanks for your time.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Unique values from array then average adjacent column values

    If you are using UNIQUE and FILTER, then you must be using Excel 365. Please update your profile to get version appropriate solutions offered.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Unique values from array then average adjacent column values

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    Manchester
    MS-Off Ver
    Excel 365
    Posts
    10

    Re: Unique values from array then average adjacent column values

    Done. Changed to 365.

  5. #5
    Registered User
    Join Date
    01-24-2013
    Location
    Manchester
    MS-Off Ver
    Excel 365
    Posts
    10

    Re: Unique values from array then average adjacent column values

    That works! I don't know how, but it does. Thanks.

    I'll have a go at applying it to other products and if happy, mark as solved.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Unique values from array then average adjacent column values

    Glad to help & thanks for the feedback.

  7. #7
    Registered User
    Join Date
    01-24-2013
    Location
    Manchester
    MS-Off Ver
    Excel 365
    Posts
    10

    Re: Unique values from array then average adjacent column values

    Would it be possible to combine this with a VLOOKUP or INDEX/MATCH to find the associated term name in the "flavour profile list" sheet?

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Unique values from array then average adjacent column values

    Do you mean like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-24-2013
    Location
    Manchester
    MS-Off Ver
    Excel 365
    Posts
    10

    Re: Unique values from array then average adjacent column values

    Exactly like that 😁😊

+ 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. Replies: 17
    Last Post: 02-02-2017, 04:13 PM
  2. Average of unique values in another column
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 07-29-2016, 03:17 PM
  3. [SOLVED] Look for duplicated & unique values in adjacent column. Perform math on result. . .
    By PeteABC123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-25-2013, 12:04 PM
  4. [SOLVED] Count Unique Values in a Column that are not in adjacent Column
    By sskgintl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2013, 03:18 PM
  5. Average range of cells in column if values in adjacent column are equal
    By RyNye in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2012, 10:12 AM
  6. Pull unique values based on adjacent column
    By freud1 in forum Excel General
    Replies: 9
    Last Post: 07-02-2012, 11:36 AM
  7. Replies: 3
    Last Post: 03-17-2012, 11:32 PM

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