(solved)Need help with formulat :IF(condition),Average(if...

[zeriab]

Hi All,

Trying to calculate the AgeAvg of pending order for specific planner (cellsA5). the result given is #N/A.

Pls help

=IF(C5>0,AVERAGE(IF('#'!$BE$2:$BE$3179="Pending",IF('#'!$M$2:$M$3112=A5,'#'!$BC$2:$BC$3179))),"--")


Regards

Zeriab

 

[pgc01]

Hi Zeriab

In an array formula all arrays must have the same size, or else at some point there are values not available (#N/A).

In your case 2 of the arrays go from row 2 to row 3179, and the third goes from row 2 to row 3112. This is not possible.

for example:

=IF(C5>0,AVERAGE(IF('#'!$BE$2:$BE$3179="Pending",IF('#'!$M$2:$M$3179=A5,'#'!$BC$2:$BC$3179))),"--")

Hope this helps
PGC