Excel 2007 : Average certain cells of 5 worksheets only if other cells meet criteria

[jaedmar]

I have six worksheets. The first 5 are identical with different information in cells.
In a cell on worksheet 6, I want to get the average cell B10 from the first five worksheets only if cell B15 on each sheet = "F".
So, if one of the worksheets does not have "F" in B15, I do not want to include B10 from that worksheet in the average.
How do I write that?

[MarvinP]

Hi jaedmar and welcome to the forum
This sounds like a fun problem, but is a little vague. We need to know what range of cells are being averaged from the first 5 worksheets. Could you create a small sample file with 6 worksheets and attach it for us to use? To do this click on "Go Advanced" under the message area and then on the PaperClip above the message area to attach a sample file.

[DonkeyOte]

Given the small number of sheets the following would suffice and be relatively efficient (and non-volatile)

Please Login or Register  to view this content.

the alternatives would be:

a) to add a single cell on each sheet (identical) that has the above IF approach:

Please Login or Register  to view this content.

then

Please Login or Register  to view this content.

b) use an inefficient & volatile Array

Please Login or Register  to view this content.

though it should be noted that unlike the standard AVERAGE the use of N in the Array would convert non-numbers to 0 which would distort the average

[MarvinP]

If you wanted to see a User Defined Function that did the above, it would look like:

Please Login or Register  to view this content.

The above could be parameterized to to give the cell being summed and criteria being met but won't do unless it is a valid answer.

[DonkeyOte]

@MarvinP, if you opt against passing the precedent ranges explicitly you will need to make your UDF volatile such that alterations on Sheets 1 to 5 are reflected in the UDF result as and when they occur.

It would be better (IMO) to pass the range explicitly to avoid this requirement.

edit:
in retrospect, given the fact that passing 3D ranges to a UDF is not really possible (other than passing as string and converting) I'd say better to make Volatile (!)
[I shuddered just a little as I typed that last bit...]

[jaedmar]

I am about to try these options out. In the meantime, here is a Sample which shows exactly what I am doing.
Sheets 1-5 represent peoples data. If, on any sheet, B15=F, then I want to include B10 from that sheet in a formula to show the average of those people in B18 of the 6th worksheet title AVERAGES.

[DonkeyOte]

jaedmar, it would perhaps be a good idea to reload the file with some sample data points and expected results.

[jaedmar]

Here is a new attachment with data in the cells. I have put a note on each of the six pages explaining the true and false conditions and the expected result.

In short, here are the desired effects and conditions:
Person 1 (Of the three dates for which data was recoreded, in the most recent, D15 = "F", so D10 should (196) be included in the average formula.)
Person 2 (Of the two dates for which data was recoreded, in the most recent, C15="F", so C10 (158) should be included in the average formula.)
Person 3 (Of the three dates for which data was recoreded, in the most recent, D15 is not "F", so D10 should not be included in the average formula.)
Person 4 (Of the two dates for which data was recoreded, in the most recent, C15="F", so C10 (200) should be included in the average formula.)
Person 5 (Of the three dates for which data was recoreded, in the most recent, D15 is not "F", so D10 should not be included in the average formula.)

Averages (So, the cells with the most recent data in Weight row on the sheets with "F" in the most recent column would be inculded in the average. Those are Person 1 D10, Person 2 C10, and Person 4 C10)

[DonkeyOte]

Not pretty but assuming I've understood then given the version (XL2007) and desire to avoid volatiles one approach might be along the lines of the attached...

edit 2: attachment removed in light of below points - new attachment in subsequent post

Uses a CHOOSE to generate 5 results with LOOKUP in each and IFERROR handlers

edit 1:
I suspect the attached is wrong as I've used the last record for each type in the Average whereas above perhaps implies only the last record is ever included
(ie max 1 number per person is averaged be it in L, E or F)

[DonkeyOte]

In light of above - new attachment added (prior removed) - latest shows both methods (Cols B & C)

Col C approach assumes that for each Title entry a number entry shall exist also

[jaedmar]

Impressive. Thank you. I think that works.
If over time, I add people (worksheets) to the spreadsheet, what's the best way to add them into the formula? One at a time is surely possible, but is there a way to just put a range of worksheets?

[DonkeyOte]

If you wish to use a variable sheet list etc then (IMO) you will be better off reverting to a Volatile UDF.
I say that because an equivalent native function would be Volatile also and is almost too hideous to comprehend.
(the last formula in post #3 is bad enough and that's not even accounting for last entries etc and is not bullet proof)

Either way IMO you would be better off adding some very basic helper cells to each individual sheet to reduce the complexity of the calculation.

[DonkeyOte]

Attached is the earlier sample file with the addition of a UDF approach.

The UDF calls are in Col D and use the Sheet List as defined in Col J.
As you adjust that sheet list (eg remove Person 5 or Person 4) so the UDF returns will adjust accordingly.

Using Volatile UDF's is not great though in terms of performance....

[jaedmar]

Whew!

Here is what I have got so far, and it works!

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheetlist&"'!b15"),"F",INDIRECT("'"&sheetlist&"'!d8")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!b15"),"F"))

Now, my last question is: If there is no data entered in the cells from which the formula pulls, how do I just get it to leave this cell blank, rather than "0"?

[DonkeyOte]

Not entirely sure I follow the requirements... your calc. above is effectively:

=SUMIF/COUNTIF

Perhaps:

Please Login or Register  to view this content.

the above would only include numbers - ie text/nulls in D8 would be excluded from the Average

The above formula still doesn't account for last values etc... which was seemingly a requirement ?

Should sheetlist contain any references that are invalid then #REF! will result.

The UDF provided previously accounts for both of the above - both approaches are volatile obviously.

[DonkeyOte]

@jaedmar,

Your post does not comply with Rule 8 of our Forum RULES.

Cross-posting is when you post the same question in other forums on the web (eg MrExcel)

You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.

We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this