Conditional average over multiple sheets?

[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!

[Domenic]

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!

[Teethless mama]

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"))))

[remps]

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

[remps]

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!

[Domenic]

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

[remps]

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

[Domenic]

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?

[remps]

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?

[Domenic]

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!

[remps]

Got it! Thanks again for all the help!

[remps]

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.

[Domenic]

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!

[remps]

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

Thanks again! I owe you a beer