+ Reply to Thread
Results 1 to 3 of 3

Average unique values from an index

  1. #1
    Registered User
    Join Date
    11-01-2019
    Location
    Ann Arbor
    MS-Off Ver
    2019
    Posts
    1

    Average unique values from an index

    Hello everyone!

    I need some help with a good formula for an issue I'm having with excel, I need to calculate the average of a range based on an index.

    I have this formula from another thread but now I need to link it to the index "=AVERAGE(IF(FREQUENCY(AQ5:AQ12,AQ5:AQ12),AQ5:AQ12))"

    I attached a pic and an excel file with example values, in the pic, I want to calculate the average value from unique values on AQ column for a range given in the index on AJ column.

    I hope it makes sense from the pic and excel file attached.

    example.JPG
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Average unique values from an index

    Your description and sheet do not tally. In particular, what do you mean by the bit in red. I assume that AQ is now column H....

    I want to calculate the average value from unique values on AQ column for a range given in the index on AJ column.
    Glenn



  3. #3
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Average unique values from an index

    Hi ,

    This seems to work :

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


    This is an array formula , to be entered using CTRL SHIFT ENTER.

    The first reference $A$2:$A$34 is one cell more than the data range because the FREQUENCY function returns an array which is one element more than the data range.

    Narayan

+ 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: 2
    Last Post: 07-30-2018, 02:21 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. Average Unique Values
    By SA16 in forum Excel General
    Replies: 10
    Last Post: 07-04-2015, 04:19 PM
  4. [SOLVED] Using INDEX MATCH to return unique values for non-unique search term
    By rico_suave in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-03-2015, 01:53 AM
  5. [SOLVED] Average unique values with condition
    By emortals in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-21-2015, 07:51 AM
  6. [SOLVED] average for unique values
    By ammartino44 in forum Excel General
    Replies: 7
    Last Post: 09-16-2014, 05:34 PM
  7. [SOLVED] A formula to return the average of unique values only
    By simonlblea in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-08-2013, 01:22 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