merging 3x average(if arrays to give average of all 3

[neowok]

I have the following array formula

=AVERAGE(IF(('Phase 2B'!$BN$3:$BN$1370>0)*('Phase 2B'!$AA$3:$AA$1370>TODAY()-365),'Phase 2B'!$BN$3:$BN$1370)+IF(('Phase 2A'!$BY$3:$BY$1500>0)*('Phase 2A'!$Z$3:$Z$1500>TODAY()-365),'Phase 2A'!$BY$3:$BY$1500)+IF(('RSZ Phase 1'!$CB$3:$CB$1500>0)*('RSZ Phase 1'!$Z$3:$Z$1500>TODAY()-365),'RSZ Phase 1'!$CB$3:$CB$1500))*0.71

Which works fine for calculating the average on a phase 2b sheet. But I now need to calculate the average over phase 2b and phase 1. I have the same array formulas to do each sheet on its own and they work fine, but have not been able to merge them into a single formula to get the average over all 3 sheets. I came up with this (as array) but it just returns N/A

=AVERAGE(IF(('Phase 2B'!$BN$3:$BN$1370>0)*('Phase 2B'!$AA$3:$AA$1370>TODAY()-365),'Phase 2B'!$BN$3:$BN$1370)+IF(('Phase 2A'!$BY$3:$BY$1500>0)*('Phase 2A'!$Z$3:$Z$1500>TODAY()-365),'Phase 2A'!$BY$3:$BY$1500)+IF(('Phase 1'!$CB$3:$CB$1500>0)*('Phase 1'!$Z$3:$Z$1500>TODAY()-365),'Phase 1'!$CB$3:$CB$1500))*0.71

 

[Oscar Cronquist]

You can't add cell ranges or arrays with different sizes, that will return an error.

Try this:

=AVERAGE(IF(('Phase 2B'!$BN$3:$BN$1370>0)*('Phase 2B'!$AA$3:$AA$1370>TODAY()-365),'Phase 2B'!$BN$3:$BN$1370),IF(('Phase 2A'!$BY$3:$BY$1500>0)*('Phase 2A'!$Z$3:$Z$1500>TODAY()-365),'Phase 2A'!$BY$3:$BY$1500),IF(('Phase 1'!$CB$3:$CB$1500>0)*('Phase 1'!$Z$3:$Z$1500>TODAY()-365),'Phase 1'!$CB$3:$CB$1500))*0.71

 

[neowok]

Ahh I forgot they were different ranges (could make them the same size anyway, a lot of the bottom rows are blank to capture any new data they enter).

Yours works anyway, thanks.