# Combining multiple array formulas

#### Garrix

##### New Member
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:
Code:
`````` =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?

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi,
just to clarify, you want to simplify the formulae or combine the 3 seen in your thread into one?

Hi,
just to clarify, you want to simplify the formulae or combine the 3 seen in your thread into one?

Hey again!

I would like to (if possible), combine all 3 formulas and then average them - in a single cell. Right now I'm using 4 cells to do that, and need a hidden sheet for the calculations.

so for:
 1PIEB2 1 1PIEB3 2 1PIEB4 3 1PIEB5 4 1PIEB6 5 1PIEB7 6 1PIEB8 7 1PIEA3 1 1PIEA4 1PIEA5 4 1PIEA6 5 1HIFG5 10

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

you would like to see not 3.333, 4 and nothing but just 3.667?

so for:
 1PIEB2 1 1PIEB3 2 1PIEB4 3 1PIEB5 4 1PIEB6 5 1PIEB7 6 1PIEB8 7 1PIEA3 1 1PIEA4 1PIEA5 4 1PIEA6 5 1HIFG5 10

<tbody>
</tbody>

you would like to see not 3.333, 4 and nothing but just 3.667?

Exactly!

so for:
 1PIEB2 1 1PIEB3 2 1PIEB4 3 1PIEB5 4 1PIEB6 5 1PIEB7 6 1PIEB8 7 1PIEA3 1 1PIEA4 1PIEA5 4 1PIEA6 5 1HIFG5 10

<TBODY>
</TBODY>

you would like to see not 3.333, 4 and nothing but just 3.667?

That would mean taking the average of a set of conditional averages!

 1PIEB2 1 PIEA PIEB PIEC 1PIEB3 2 3.333333 4 #DIV/0! 3.666667 1PIEB4 3 1PIEB5 4 1PIEB6 5 1PIEB7 6 1PIEB8 7 1PIEA3 1 1PIEA4 1PIEA5 4 1PIEA6 5 1HIFG5 10

<COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" width=74><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 824" span=2 width=23><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

E2, copied across to G2, calculates the conditional averages:
Rich (BB code):
``````=AVERAGEIF(\$A\$1:\$A\$12,"*"&E1&"*",\$B\$1:\$B\$12)
``````
H2 calculates in H2 the average of the averages in E2:G2 with:
Rich (BB code):
``````=AVERAGEIF(E2:G2,"<"&9.99E+307)
``````
If such is intended, the foregoing is a fast set up. If you still insist on calculating such an average of averages by means of a single formula:

Control+shift+enter, not just enter...
Rich (BB code):
``````=AVERAGE(IF(ISNUMBER(AVERAGEIF(\$A\$1:\$A\$12,"*"&{"PIEA";"PIEB";"PIEC"}&"*",\$B\$1:\$B\$12)),
AVERAGEIF(\$A\$1:\$A\$12,"*"&{"PIEA";"PIEB";"PIEC"}&"*",\$B\$1:\$B\$12)))``````

Thanks for the feedback... so wildcards should be placed prior to curly bracket... well noted...

Aladin, what would be the best approach if we wanted to get the average of all values considering the three conditions, instead of the average of averages?

Last edited:
Aladin, what would be the best approach if we wanted to get the average of all values considering the three conditions, instead of the average of averages?

Control+shift+enter, not just enter:
Rich (BB code):
``````=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...
Rich (BB code):
``````=AVERAGE(IF(ISNUMBER(SEARCH(E1:G1,\$A\$1:\$A\$12)),
IF(ISNUMBER(\$B\$1:\$B\$12),\$B\$1:\$B\$12)))
``````

That would mean taking the average of a set of conditional averages!

 1PIEB2 1 PIEA PIEB PIEC 1PIEB3 2 3.333333 4 #DIV/0! 3.666667 1PIEB4 3 1PIEB5 4 1PIEB6 5 1PIEB7 6 1PIEB8 7 1PIEA3 1 1PIEA4 1PIEA5 4 1PIEA6 5 1HIFG5 10

<tbody>
</tbody>

E2, copied across to G2, calculates the conditional averages:
Rich (BB code):
``````=AVERAGEIF(\$A\$1:\$A\$12,"*"&E1&"*",\$B\$1:\$B\$12)
``````
H2 calculates in H2 the average of the averages in E2:G2 with:
Rich (BB code):
``````=AVERAGEIF(E2:G2,"<"&9.99E+307)
``````
If such is intended, the foregoing is a fast set up. If you still insist on calculating such an average of averages by means of a single formula:

Control+shift+enter, not just enter...
Rich (BB code):
``````=AVERAGE(IF(ISNUMBER(AVERAGEIF(\$A\$1:\$A\$12,"*"&{"PIEA";"PIEB";"PIEC"}&"*",\$B\$1:\$B\$12)),
AVERAGEIF(\$A\$1:\$A\$12,"*"&{"PIEA";"PIEB";"PIEC"}&"*",\$B\$1:\$B\$12)))``````

I'm getting a div/0 error, not sure if I may have screwed it up. I'm basically trying to use this formula in combination with the one you gave me in the other thread

Rich (BB code):
``=SUM(IF(ISNUMBER(MATCH(TRIM(\$B\$2:INDEX(\$B:\$B,MATCH(REPT("z",255),\$B:\$B))),   J2:INDEX(J:J,MATCH(REPT("z",255),J:J)),0)),1))``

I'm trying to average from that list of values, then average the total average in each column. Maybe I should have started with that?

Replies
3
Views
129
Replies
1
Views
100
Replies
10
Views
468
Replies
3
Views
129
Replies
2
Views
210

1,203,524
Messages
6,055,905
Members
444,832
Latest member
bgunnett8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back