+ Reply to Thread
Results 1 to 8 of 8

INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

  1. #1
    Registered User
    Join Date
    06-12-2019
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    Attachment 627964

    Hi, i am trying to do this:

    =AVERAGEIFS(INDIRECT(D$1);INDIRECT(D$2);">="&$C7;INDIRECT(D$2);"<"&$B7)
    In file "scores per day", I want to take the average for the data that is in between the timestamps (which is in column B in file_6). The timestamps basically specify a specific second at a certain day. Each company is in a different sheet, which is why I am using the INDIRECT formula. Column C in file_6 corresponds to the average range. Furthermore I have multiple excel files such as file_6. So the file name in row 4 of the "scores per day" file is different per company as I have multiple files. Therefore please do not only use a reference to file 6 but I need to make sure it refers to row 4 as I did right now. I have about 2000 companies spread across several files and therefore a manual reference is not going to work. I need an indirect reference to the file, sheet and range which is specified for each company. For simplicity I just attached one of the files (file 6) right now that I use to look data up, but I have multiple ones like this.

    I have the other files open so the INDIRECT should work. Does anyone know what I did wrong?

    Any help would be appreciated, thanks in advance. I also added a file in which I show what I am trying to achieve (but I need an INDIRECT reference to a file, since I have a different file per company).
    Last edited by lisannexxx; 06-13-2019 at 07:26 AM. Reason: adding files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    06-12-2019
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    Hi Pepe,

    I think it is clear what I am trying to achieve from the formula together with my explanation. I would like it to take the average for the first of January 2016 for example but the data in the other files is in timestamps, therefore I first converted it and then I look for the average in between the timestamps in column B and C in the picture. The data is sensitive.

    You replied one minute after my post so I don't believe you fully read it.

    Let me know if anything is unclear from the picture.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    You can't expect members helping you for free on their spare time to retype examples to test them.
    If you don't want to add a sample ( eventually a mock up) so be it.
    Good luck with your query

  5. #5
    Registered User
    Join Date
    06-12-2019
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    Hi Pepe, You're right, sorry, I thought it was a simple solvable question and therefore I thought a picture would be enough. I thought the answer would be quite easy and straightforward. Also because it is sensitive data I didnt want to upload the files. I attached the files and eliminated sensitive data.

    Sorry again!

  6. #6
    Registered User
    Join Date
    06-12-2019
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    For anyone trying to solve this problem in the future, since the sheet name consisted of a bar (-), Excel bugged. If you delete the bars from the sheet name, it should work. The formula itself is working fine once you delete the bar from the sheet names and in the formula.

  7. #7
    Registered User
    Join Date
    06-12-2019
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    And apparently excel also bugs if you concatenate the header of the table for the indirect file reference. Instead, refer to the regular cell reference.


    Excel has its limits

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: INDIRECT not working / AVERAGEIFS with criteria bigger or smaller than cell reference

    Quote Originally Posted by lisannexxx View Post
    Hi Pepe, You're right, sorry, I thought it was a simple solvable question and therefore I thought a picture would be enough. I thought the answer would be quite easy and straightforward. Also because it is sensitive data I didnt want to upload the files. I attached the files and eliminated sensitive data.

    Sorry again!
    No worries. Thank you for helping us to help you

+ 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. How to know colum category of the bigger/smaller value
    By Lbazer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2019, 08:40 PM
  2. [SOLVED] How to change a value according to a rule - bigger than, smaller than
    By olibeira in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-26-2017, 04:40 PM
  3. Averageifs using cell reference in criteria
    By Zozee1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-04-2014, 09:23 AM
  4. Using AVERAGEIFS function to average a smaller subset of criteria data
    By RJS5062 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-22-2012, 04:19 PM
  5. [SOLVED] AVERAGEIFS using a cell reference in the criteria
    By John007 in forum Excel General
    Replies: 2
    Last Post: 01-10-2012, 05:22 PM
  6. formula to check bigger and smaller than
    By avengine in forum Excel General
    Replies: 1
    Last Post: 12-26-2008, 03:13 PM
  7. How to find value that is bigger or smaller than 2SD?
    By learner in forum Excel General
    Replies: 8
    Last Post: 05-11-2005, 05:06 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