+ Reply to Thread
Results 1 to 8 of 8

Hlookup/Match/Indirect formula to average

  1. 05-11-2017, 09:56 AM #1
    Andyc6
    Andyc6 is offline
    Registered User
    Join Date
    01-19-2017
    Location
    West Midlands
    MS-Off Ver
    Excal 2010
    Posts
    26

    Hlookup/Match/Indirect formula to average

    Hello

    Through the help of this forum i have managed to develop the below formula which works a treat. But i now want to develop it further. In 90% of instances the information in Cell B31 only appears once on the spreadsheet it is going to but there are a few occasions it appears 4 or 5 times, i would like for those instances for it to return the average or minimum value as at the moment it returns the answer for the first row it finds a match.

    =IF(HLOOKUP(C$6,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$AG$196"),MATCH(B31,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$A$196"),0),0)="","",HLOOKUP(C$6,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$AG$196"),MATCH(B31,INDIRECT("'"&E$4&" "&"Compliance"&"'!"&"$A$11:$A$196"),0),0))

    Again any help received is much appreciated
    Reply With QuoteRegister To Reply
  2. 05-11-2017, 10:36 AM #2
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Hlookup/Match/Indirect formula to average

    My wee brain can't work its way through a formula like that.



    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 05-11-2017, 12:07 PM #3
    Andyc6
    Andyc6 is offline
    Registered User
    Join Date
    01-19-2017
    Location
    West Midlands
    MS-Off Ver
    Excal 2010
    Posts
    26

    Re: Hlookup/Match/Indirect formula to average

    Please see attached extract from my excel sheet
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 05-11-2017, 12:23 PM #4
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Hlookup/Match/Indirect formula to average

    Assuming that column L on your 1 Compliance sheet is always column L....


    =AVERAGEIF(INDIRECT("'"&E$4&" Compliance"&"'!"&"A11:$A196"),B31,INDIRECT("'"&E$4&" Compliance"&"'!"&"L11:L196"))
    Reply With QuoteRegister To Reply
  5. 05-11-2017, 12:35 PM #5
    Andyc6
    Andyc6 is offline
    Registered User
    Join Date
    01-19-2017
    Location
    West Midlands
    MS-Off Ver
    Excal 2010
    Posts
    26

    Re: Hlookup/Match/Indirect formula to average

    Thanks, Column L can be fixed if needed, but that dosent work if column L is blank it returns #DIV/O! instead of -
    Reply With QuoteRegister To Reply
  6. 05-11-2017, 01:05 PM #6
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Hlookup/Match/Indirect formula to average

    That was the least of the problems...

    =IFERROR(AVERAGEIF(INDIRECT("'"&E$4&" Compliance"&"'!"&"A11:$A196"),B31,INDIRECT("'"&E$4&" Compliance"&"'!"&"L11:L196")),"")

    or

    =IFERROR(AVERAGEIF(INDIRECT("'"&E$4&" Compliance"&"'!"&"A11:$A196"),B31,INDIRECT("'"&E$4&" Compliance"&"'!"&"L11:L196")),0)

    depending on your aesthetic preferences. The original formula looked a bit overcomplicated (I do it all the time, too...) IF column L doesn't move around...
    Reply With QuoteRegister To Reply
  7. 05-12-2017, 02:06 AM #7
    Andyc6
    Andyc6 is offline
    Registered User
    Join Date
    01-19-2017
    Location
    West Midlands
    MS-Off Ver
    Excal 2010
    Posts
    26

    Re: Hlookup/Match/Indirect formula to average

    Thanks, that works i have tweaked slightly so it gives me a "-". What if column L did move around, is it still possible?
    Reply With QuoteRegister To Reply
  8. 05-12-2017, 03:40 AM #8
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Hlookup/Match/Indirect formula to average

    This will work if L goes off a-wandering...

    =IFERROR(AVERAGEIF(INDIRECT("'"&E$4&" Compliance'!A11:A196"),B31,INDIRECT("'"&E$4&" Compliance'!"&SUBSTITUTE(ADDRESS(1,MATCH($C$6,INDIRECT("'"&E$4&" Compliance'!A11:AG11"),0),4),1,"")&"11:"&SUBSTITUTE(ADDRESS(1,MATCH($C$6,INDIRECT("'"&E$4&" Compliance'!A11:$AG11"),0),4),1,"")&"196")),"")

    If it does not, the original formula can be further trimmed to:

    =IFERROR(AVERAGEIF(INDIRECT("'"&E$4&" Compliance'!A11:$A196"),B31,INDIRECT("'"&E$4&" Compliance'!L11:L196")),"")
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. #N/A returned with HLOOKUP and MATCH formula
    By Andyc6 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2017, 04:03 AM
  2. [SOLVED] Advanced VLOOKUP/MATCH/HLOOKUP Formula
    By Darko_Giac in forum Excel General
    Replies: 3
    Last Post: 12-22-2015, 12:08 PM
  3. [SOLVED] Hlookup & match formula
    By puffyboy in forum Excel General
    Replies: 3
    Last Post: 09-26-2015, 12:41 AM
  4. [SOLVED] #Value error for hlookup, match and Index combine formula
    By Pi* in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-05-2015, 07:09 PM
  5. autofill indirect formula with moving average
    By jeff33 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2015, 07:36 PM
  6. Add a value to a specific cell using a hlookup and match formula
    By lnh2204 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2013, 02:06 PM
  7. [SOLVED] Hlookup and Average Formula
    By abduljaleel.mca in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2013, 08:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1