+ Reply to Thread
Results 1 to 4 of 4

integration of IF, INDIRECT and AVERAGE function in formula

  1. #1
    Registered User
    Join Date
    04-25-2020
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    7

    integration of IF, INDIRECT and AVERAGE function in formula

    Hello,

    In a previous post I got a great answer on the calculation of an average with several conditions. However, I need now a combination of that formula with IF and INDIRECT function.I think it has to begin with: IF(ISTEXT($A7),AVERAGE(INDIRECT(A7&"!"&" , after this I don’t know how to combine the both. In the attachment there is an example

    The average formula: =AVERAGE(INDEX(C2:C781,MATCH(1,N(B2:B781>=122),0)):C1048576)

    Many thanks in advance.

    Kind regards,
    I.L
    Last edited by I.L2020; 10-06-2020 at 02:47 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: integration of IF, INDIRECT and AVERAGE function in formula

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-25-2020
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    7

    Re: integration of IF, INDIRECT and AVERAGE function in formula

    thank you for the notificantion I altered my post with a dummy workbook in it

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: integration of IF, INDIRECT and AVERAGE function in formula

    Note that the text in cell A7 is not exactly the same as the name for the Data Participant 1 sheet. Select and copy the sheet name from the tab and then paste it into cell A7 before pasting the formula into cell B7.
    Then the following array entered formula** works:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Hlookup/Match/Indirect formula to average
    By Andyc6 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2017, 03:40 AM
  2. Error occuring in Integration of 2 sheets using hlookup function
    By Mike Santuile in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-02-2016, 02:05 AM
  3. autofill indirect formula with moving average
    By jeff33 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2015, 07:36 PM
  4. integration function
    By uncina in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-23-2009, 05:38 AM
  5. Integration of a function over some interval
    By jens1234 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-13-2008, 08:25 PM
  6. IF function for scheduling tool with outlook integration
    By systematic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-16-2005, 09:10 PM
  7. Trouble with Average & Indirect Function
    By CENorth7272 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2005, 10:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1