# Conditional average over multiple sheets?

1. ## 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!  Register To Reply

2. 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!  Register To Reply

3. 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"))))  Register To Reply

4. 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  Register To Reply

5. 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!  Register To Reply

6. 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))  Register To Reply

7. 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   Register To Reply

8. 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?  Register To Reply

9. 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?  Register To Reply

10. 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!  Register To Reply

11. Got it! Thanks again for all the help!  Register To Reply

12. ## 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.  Register To Reply

13. 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!  Register To Reply

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

Thanks again! I owe you a beer   Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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