I have the following array formula
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
Code:
=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
Code:
=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