Combining multiple array formulas

[Garrix]

I have several formulas used to calculate the average of a bunch of data points. Currently, I have to have a sub-section on a spare worksheet that then pulls those results back into the main sheet. I'm hoping that there is a way to bypass this, I know alot of people on this forum are hellaciously good at fixing things (as my more recent thread found..).

Current formula:

 =AVERAGE(IF(ISNUMBER(SEARCH("*PIEA*",PIESHEET!$E:$E)),IF(ISNUMBER(PIESHEET!$L:$L),PIESHEET!$L:$L,""),">0")
=AVERAGE(IF(ISNUMBER(SEARCH("*PIEB*",PIESHEET!$E:$E)),IF(ISNUMBER(PIESHEET!$L:$L),PIESHEET!$L:$L,""),">0")
=AVERAGE(IF(ISNUMBER(SEARCH("*PIEC*",PIESHEET!$E:$E)),IF(ISNUMBER(PIESHEET!$L:$L),PIESHEET!$L:$L,""),">0")

All resulting values then get put into a basic =average(result:cells).

Is there any way I can do the above in a single formula?

 

[Garrix]

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(SEARCH({"PIEA","PIEB","PIEC"},$A$1:$A$12)),
  IF(ISNUMBER($B$1:$B$12),$B$1:$B$12)))

Or with the search strings in E1:G1...

=AVERAGE(IF(ISNUMBER(SEARCH(E1:G1,$A$1:$A$12)),
  IF(ISNUMBER($B$1:$B$12),$B$1:$B$12)))

Is is simpler to somehow make it an 'if number is >0' instead of just an 'is cell number' flag? I feel like I have a major mistake somewhere, since I'm getting almost 30m differences in my averages (between your formula and my crappy way).

 

[Aladin Akyurek]

What are we going to discuss post #10 or #11? I was going to respond to #10...

 

[Garrix]

What are we going to discuss post #10 or #11? I was going to respond to #10...

I think I quoted the wrong post. Whoops!

Back to the formula:

=IFERROR(AVERAGE(IF(ISNUMBER(AVERAGEIF(Data!$E:$E,"*"&{"PIET";"PIEY";"PIEE"}&"*",DATA!$L:$L)),
  AVERAGEIF(DATA!$E:$E,"*"&{"PIET";"PIEY";"PIEE"}&"*",DATA!$L:$L))), "NA")

provides a different result than just using

=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIET*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")

for each one and then averaging the column. Any ideas?

 

[Aladin Akyurek]

I think I quoted the wrong post. Whoops!

Back to the formula:

=IFERROR(AVERAGE(IF(ISNUMBER(AVERAGEIF(Data!$E:$E,"*"&{"PIET";"PIEY";"PIEE"}&"*",DATA!$L:$L)),
  AVERAGEIF(DATA!$E:$E,"*"&{"PIET";"PIEY";"PIEE"}&"*",DATA!$L:$L))), "NA")

provides a different result than just using

=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIET*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")

for each one and then averaging the column. Any ideas?

1. Please avoid referencing whole columns for reasons of efficiency.

2. The second formula (yours) has a ">0" in there. Not clear what does that mean.

3. The first formula operates on three criteria, while the second considers just one.

 

[Garrix]

1. Please avoid referencing whole columns for reasons of efficiency.

2. The second formula (yours) has a ">0" in there. Not clear what does that mean.

3. The first formula operates on three criteria, while the second considers just one.

1. Okay. Best to pick a range and update it if it will change?

2. That's a legacy formula that I am using from the old spreadsheet. I thought that the >0 was a 'only if greater than 0' criteria?

3. Nono, I mean If

=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIET*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")
=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIEY*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")
=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIEE*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")

and do a =average(aboveresults)

I get a different result. Unless I was just unclear on my earlier question?

 

[Aladin Akyurek]

1. Okay. Best to pick a range and update it if it will change?

2. That's a legacy formula that I am using from the old spreadsheet. I thought that the >0 was a 'only if greater than 0' criteria?

3. Nono, I mean If

=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIET*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")
=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIEY*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")
=IFERROR(AVERAGE(IF(ISNUMBER(SEARCH("*PIEE*",DATA!$E:$E)),IF(ISNUMBER(DATA!$L:$L),DATA!$L:$L,""),">0")),"NA")

and do a =average(aboveresults)

I get a different result. Unless I was just unclear on my earlier question?

Try to create a tiny sample where the formulas do differ?

 

[Garrix]

Try to create a tiny sample where the formulas do differ?

Thanks for sticking with this. I found out again that I was asking the wrong question

=AVERAGE(IF(ISNUMBER(SEARCH({"PIEA","PIEB","PIEC"},$A$1:$A$12)),   IF(ISNUMBER($B$1:$B$12),$B$1:$B$12)))

Or with the search strings in E1:G1...
Code:


=AVERAGE(IF(ISNUMBER(SEARCH(E1:G1,$A$1:$A$12)),   IF(ISNUMBER($B$1:$B$12),$B$1:$B$12)))</pre>

I need the above, not the average of averages. The first one works fine, but the second one gives a DIV/0 error. Any ideas? From what I can see, they are almost the same. It doesn't seem to like me using A3:A10 instead of explicitly naming them it seem.

 

[Aladin Akyurek]

Thanks for sticking with this. I found out again that I was asking the wrong question

=AVERAGE(IF(ISNUMBER(SEARCH({"PIEA","PIEB","PIEC"},$A$1:$A$12)),   IF(ISNUMBER($B$1:$B$12),$B$1:$B$12)))

Or with the search strings in E1:G1...
Code:


=AVERAGE(IF(ISNUMBER(SEARCH(E1:G1,$A$1:$A$12)),   IF(ISNUMBER($B$1:$B$12),$B$1:$B$12)))
</PRE>

I need the above, not the average of averages. The first one works fine, but the second one gives a DIV/0 error. Any ideas? From what I can see, they are almost the same. It doesn't seem to like me using A3:A10 instead of explicitly naming them it seem.

That shouldn't happen. See the workbook that shows this observation:
https://dl.dropboxusercontent.com/u/65698317/Garrix%20If%20Conditioned%20Average.xlsx