+ Reply to Thread
Results 1 to 3 of 3

Simplify The Indirect, AverageIFs and IFError Formula

  1. #1
    Registered User
    Join Date
    05-05-2014
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    1

    Simplify The Indirect, AverageIFs and IFError Formula

    I am using the following formula and it is working fine. Is there a better way to do so?

    =IFERROR(AVERAGEIFS(INDIRECT("Data!$U"&$B$1&":$U"&$A$1),INDIRECT("Data!$A"&$B$1&":$A"&$A$1),">="&D$4,INDIRECT("Data!$A"&$B$1&":$A"&$A$1),"<="&D$5,INDIRECT("Data!$U"&$B$1&":$U"&$A$1),"<>0"),"-")

    I am looking for a better way as I have to replicate the same formula in 100 columns and in 60 rows. Where every row use a different target column (like the above mentioned is using column U.

    Indirect function gets the values from A1 and B1 cells (Start and end of column because end value keeps changing with data updates)
    AverageIfs get average of a particular value between two dates mentioned on top.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Simplify The Indirect, AverageIFs and IFError Formula

    Hi,

    With such a formula it will be difficult to suggest improvements without seeing it in context, i.e. within an actual workbook.

    Kindly upload one, desensitizing data if necessary.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,628

    Re: Simplify The Indirect, AverageIFs and IFError Formula

    instead of
    Please Login or Register  to view this content.
    you can use
    Please Login or Register  to view this content.
    or after appropriate changes in A1 and B1, to be now "distance from row 1" (i.e 0 if in row 1) and number of rows to be included respectively:
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

+ 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. Help to simplify an =IF(IFERROR(INDEX(...,MATCH( formula
    By sa12345 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2014, 03:40 AM
  2. xlfn.IFERROR or IFERROR don't work in Excel 98-2003
    By dj_danu01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2013, 02:53 AM
  3. Replies: 1
    Last Post: 07-19-2012, 05:36 PM
  4. [SOLVED] Excel 2007 : Help (again) with AVERAGEIFS formula
    By milliemoo in forum Excel General
    Replies: 9
    Last Post: 03-27-2012, 09:37 AM
  5. Need help averageifs formula
    By lliam79 in forum Excel General
    Replies: 3
    Last Post: 09-23-2011, 02:44 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