+ Reply to Thread
Results 1 to 14 of 14

Conditional average over multiple sheets?

  1. #1
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9

    Conditional average over multiple sheets?

    Iím working on a formula that will average a set of values over multiple sheets, given that another set of cell references are = 100%. The cell locations are constant over multiple sheets. I tried doing this on one sheet as a test and came up with this formula which works:

    =AVERAGE(IF(A2:C2=100%,A3:C3))

    However, when I try to apply this to multiple sheets I canít get it to work. The closest I can get is this:

    =IF(AND(A2=100%,Sheet2!A2=100%,Sheet3!A2=100%),AVERAGE(Sheet1:Sheet3!A3),2)

    but the problem is, if one value is not equal to 100%, then it doesnít average anything, it just gives me my error message, which is 2 in this case. From my very limited knowledge, it doesnít look like you can use IF with an array fxnÖ.maybe thereís another way?

    Any advice would be greatly appreciated. Iíll attach my excel file so you can see what Iím referring to.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Let G2:G4 contains the sheet names (Sheet1, Sheet2, and Sheet3), then try...

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$4&"'!A2:C2"),100%,INDIRECT("'"&$G$2:$G$4&"'!A3:C3")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$G$2:$G$4&"'!A2:C2"),100%))

    or

    =AVERAGE(IF(N(OFFSET(INDIRECT("'"&$G$2:$G$4&"'!A2:C2"),,COLUMN($A$2:$C$2)-COLUMN($A$2),,1))=100%,N(OFFSET(INDIRECT("'"&$G$2:$G$4&"'!A3:C3"),,COLUMN($A$2:$C$2)-COLUMN($A$2),,1))))

    Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211
    Quote Originally Posted by remps
    Iím working on a formula that will average a set of values over multiple sheets, given that another set of cell references are = 100%. The cell locations are constant over multiple sheets. I tried doing this on one sheet as a test and came up with this formula which works:

    =AVERAGE(IF(A2:C2=100%,A3:C3))

    However, when I try to apply this to multiple sheets I canít get it to work. The closest I can get is this:

    =IF(AND(A2=100%,Sheet2!A2=100%,Sheet3!A2=100%),AVERAGE(Sheet1:Sheet3!A3),2)

    but the problem is, if one value is not equal to 100%, then it doesnít average anything, it just gives me my error message, which is 2 in this case. From my very limited knowledge, it doesnít look like you can use IF with an array fxnÖ.maybe thereís another way?

    Any advice would be greatly appreciated. Iíll attach my excel file so you can see what Iím referring to.

    Thanks!
    Try this:

    =AVERAGE(IF(N(INDIRECT("Sheet"&{1,2,3}&"!A2"))=1,N(INDIRECT("Sheet"&{1,2,3}&"!A3"))))

  4. #4
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9
    Thanks so much guys! I've been swamped with some other stuff @ work, so I haven't had a chance to work on this yet....I'll keep you updated.

    Thanks again

  5. #5
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9
    Dominic - thanks for the help...the formula's both worked great.

    Teethless mama - I couldn't get your formula to work, but I'm VERY interested in getting it to work! When I paste it in the Test sheet I posted I get "#NAME?" in the cell. I've been breaking it down trying to learn what's wrong, but it's a slow process, as there's a couple functions I've never used before...

    Thanks for the help both of you!

    EDITED: Toothless - Nevermind - it works great! There was a space between the second "N(DIRECT" in the formula that I copied, and just had to delete!
    Last edited by remps; 05-08-2008 at 07:22 PM.

  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by remps
    Dominic - thanks for the help...the formula's both worked great.
    You're very welcome! Thanks for the feedback!

    EDITED: Toothless - Nevermind - it works great! There was a space between the second "N(DIRECT" in the formula that I copied, and just had to delete!
    Then it looks like I misunderstood your intent. I understood that A2:C3 on each sheet contained the data, as your first formula shows...

    =AVERAGE(IF(A2:C2=100%,A3:C3))
    Last edited by Domenic; 05-08-2008 at 08:55 PM.

  7. #7
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9
    Quote Originally Posted by Domenic

    Then it looks like I misunderstood your intent. I understood that A2:C3 on each sheet contained the data, as your first formula shows...

    =AVERAGE(IF(A2:C2=100%,A3:C3))
    No worries! I noticed that & made the adjustment - now all I have to do is incorporate this into my actual spreadsheet & I'll be set.

    You guys have given me a few new functions to play with that I've never used before - I've been dissecting those formulas carefully

  8. #8
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by remps
    No worries! I noticed that & made the adjustment - now all I have to do is incorporate this into my actual spreadsheet & I'll be set.

    You guys have given me a few new functions to play with that I've never used before - I've been dissecting those formulas carefully
    Just to be clear, I don't think adjusting the following formula...

    =AVERAGE(IF(N(INDIRECT("Sheet"&{1,2,3}&"!A2"))=1,N (INDIRECT("Sheet"&{1,2,3}&"!A3"))))

    ...to include A2:C3 on each sheet will return the desired result. Or is it your intention to include only A2:A3 on each sheet?

  9. #9
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9
    Quote Originally Posted by Domenic
    Just to be clear, I don't think adjusting the following formula...

    =AVERAGE(IF(N(INDIRECT("Sheet"&{1,2,3}&"!A2"))=1,N (INDIRECT("Sheet"&{1,2,3}&"!A3"))))

    ...to include A2:C3 on each sheet will return the desired result. Or is it your intention to include only A2:A3 on each sheet?
    Yes, my intent on that sheet was only to include A2:A3. When I said tweaking a formula I meant just changing the cell references in the ones you provided. And you were right, changing the above formula as you mentioned did NOT yield the same result.

    For my actual workbook, it looks like I will be using one cell (i.e. A2) for the percent complete, and a cell not directly connected (i.e. D3) to average from if 100%. I made the appropriate changes to your formula and it works great, and interestingly (to me anyway), taking the formula:

    =AVERAGE(IF(N(INDIRECT("Sheet"&{1,2,3}&"!A2"))=1,N (INDIRECT("Sheet"&{1,2,3}&"!A3"))))

    and changing A3 to D3 works as well. So it seems that it works for individual cell references, not for blocks I guess?

  10. #10
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    For a single cell reference, use the formula offered by Teethless mama. To reference more than one cell, you'll need to use either of the formulas I offered.

    Hope this helps!

  11. #11
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9
    Got it! Thanks again for all the help!

  12. #12
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9

    Changing Sheet references?

    I hate to beat a dead horse, but I've got a question re: this formula by Teethless mama:

    =AVERAGE(IF(N(INDIRECT("Sheet"&{1,2,3}&"!A2"))=1,N (INDIRECT("Sheet"&{1,2,3}&"!A3"))))

    How would one change the sheet references for this (say I'm using sheets named "P1-1", "P1-2", "P1-3"). For some reason, it doesn't like dashes or spaces. I've tried putting them in quotes with the dashes and/or spaces for the Sheet name and also like this:
    {-1,-2,-3}

    I'm guessing maybe in this last case it sees this as a minus sign, but I thought it should work in quotes.

    For the record, I have no problem changing Domenic's formula to work with different sheet names. I'm just really curious why I can't get the other one to work.

  13. #13
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    =AVERAGE(IF(N(INDIRECT("'"&{"P1-1","P1-2","P1-3"}&"'!A2"))=1,N (INDIRECT("'"&{"P1-1","P1-2","P1-3"}&"'!A3"))))

    ...confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

  14. #14
    Registered User
    Join Date
    04-25-2008
    Location
    Canada
    Posts
    9

    Red face

    Perfect! I don't know why I didn't think of that!?

    Thanks again! I owe you a beer

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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